MySql主从复制配置，你了解吗？来看这篇，全懂了( 四 )

2023-02-20 mysql

MySQL [(none)]> flush privileges;
Query OK, 0 rows affected (0.03 sec)
MySQL [(none)]> create user repl@'%' identified by 'repl';
Query OK, 0 rows affected (0.04 sec)
MySQL [(none)]> grant replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.05 sec)
MySQL [(none)]> flush privileges;
Query OK, 0 rows affected (0.03 sec)
MySQL [(none)]> quit;
Bye
[root@localhost bin]#
两台MySQL数据库做主从配置，假设情况如下：
主机名
IP地址
角色
参数server-id
同步用户名
同步密码
pmondbs01
.81.49
主
1
repl
repl
pmondbs02
.81.50
从
10
repl
repl
数据库基本配置参考7.2、7.3 。
数据库主从配置参数server-id的值务必不能一样。
参数server-id的值与前面章节保持一致。
检查.81.49并设置server-id参数值。
[root@pmondbs01 bin]# vi /etc/my.cnf
[mysqld]
server-id = 1
如果server-id参数值未设置为1，设置之后重启MySQL数据库，设置server-id参数值参考7.2安装MySQL 。
检查.81.50并设置server-id参数值。
[root@pmondbs02 bin]# vi /etc/my.cnf
[mysqld]
server-id = 10
如果server-id参数值未设置为10，设置之后重启MySQL数据库，设置server-id参数值参考7.2安装MySQL 。
重启MySQL使用命令
执行顺序
命令
说明
1
service mysqld stop
停止MySQL
2
service mysqld start
启动MySQL
执行示意，以在服务器pmondbs01上为例
[root@pmondbs01 ~] # service mysql stop
[root@pmondbs01 ~] # service mysql start
1. 登录81.49 MySQL数据库，执行show master status，检查并记录Master状态。记录File、Position，File为mysql-bin.000003，Position为194 。
使用命令
执行顺序
命令
说明
1
cd /usr/local/mysql/bin
进入MySQL安装目录的bin目录下
2
./mysql -uroot -p
启动MySQL客户端
3
password
输入root账号密码，进入MySQL命令行客户端。
4
show master status;
查看Master状态
执行示意
[root@pmondbs01 ~]# cd /usr/local/mysql/bin
[root@pmondbs01 bin]# ./mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.7.22-log
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MySQL [(none)]>show master status;
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------+
| mysql-bin.000002 | 3842 | | | 15aa5540-31fc-11ea-9d2d-84139f30d4bd:1-14,
4a871e1c-31eb-11ea-81b2-84139f30d4f5:13-15 |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [(none)]>
2. 登录.81.50 MySQL数据库，执行show master status，检查并记录Master状态。记录File、Position，File为mysql-bin.000003，Position为194 。
使用命令
执行顺序
命令
说明
1
cd /usr/local/mysql/bin
进入MySQL安装目录的bin目录下
2
./mysql -uroot -p
启动MySQL客户端
3
password
输入root账号密码，进入MySQL命令行客户端。
4
show master status;
查看Master状态
执行示意
[root@pmondbs02 ~]# cd /usr/local/mysql/bin
[root@pmondbs02 bin]# ./mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.7.22-log
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MySQL [(none)]>show master status;
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------+

推荐阅读

上一篇：什么是量词符？怎样理解量词符？

下一篇：2023各大厂Java面试题来喽！面试必看!