MySQL主从配置方法详解

环境介绍

• 已关闭防火墙、selinux；
• MySQL的安装请参照MySQL安装；
• 所用IP地址及角色如下表：

IP地址	角色	安装内容
192.168.91.129/24	master	mysql
192.168.91.130/24	slave	mysql

主从形式

• 一主一从
• 主主复制
• 一主多从（扩展系统读取性能，因为读是在从库读取的）
• 多主一从（5.7开始支持）
• 联级复制

检查主从环境

• 先查看master主机中主库有哪些数据库

mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
| test               |
+--------------------+

• 查看slave主机中从库有哪些数据库

[root@localhost ~]# mysql -uroot -p'123456' -e "show databases"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

• 确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中

## 全备主库时，给数据库加上读锁，避免在备份期间有其他人写入数据，导致数据不一致 ##
[root@localhost ~]# mysql -uroot -p'123456'
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.03 sec)
注：锁表后另开终端备份主库，退出数据库后将解除锁表状态

## 备份主库，并将备份文件传送到从库 ##
[root@localhost ~]# mysqldump -uroot -p'123456' --all-databases > /opt/all-$(date +'%Y%m%d-%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt
total 788
-rw-r--r--. 1 root  root  801219 Nov 10 00:00 all-20181110-000010.sql
[root@localhost ~]# scp /opt/all-20181110-000010.sql root@192.168.91.130:/root
root@192.168.91.130's password: 
all-20181110-000010.sql

• 在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致

[root@localhost ~]# mysql -uroot -p'123456' < all-20181110-000010.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p'123456' -e "show databases"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
| test               |
+--------------------+

---

主从配置

• 在主（master）数据库中创建一个同步账号授权给数据库使用

mysql> create user 'repl'@'192.168.91.130' identified by 'repl123';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.91.130';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

• 配置主（master）数据库，修改/etc/my.cnf配置文件

[root@localhost ~]# vi /etc/my.cnf
[mysqld]
......
log-bin=mysql-bin		##开启binlog日志，日志名为“mysql-bin”
server-id=1		##数据库服务器唯一标识，主库的server-id值必须比从库小
......

• 重新启动主（master）的mysqld服务，并查看主（master）库的状态

[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.... SUCCESS!
[root@localhost ~]# mysql -uroot -p'123456' -e "show master status"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

• 配置从（slave）数据库，修改/etc/my.cnf

[root@localhost ~]# vi /etc/my.cnf
[mysqld]
......
relay-log=mysql-relay-bin		##启用中继日志relay-log
server-id=2			##设置从库的唯一标识符，从库的server-id值必须大于主库的值
......

• 重启启动从（slave）的mysqld服务，以及配置并启动主从复制（注：重置主从设置命令为：reset slave all）

[root@localhost ~]# service mysqld restart
Shutting down MySQL... SUCCESS! 
Starting MySQL.... SUCCESS! 
mysql> CHANGE MASTER TO MASTER_HOST='192.168.91.129',MASTER_USER='repl',MASTER_PASSWORD='repl123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;			##开启主从同步
Query OK, 0 rows affected (0.01 sec)

• 查看从（slave）服务状态

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.91.129
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154			##读进程，执行要与这一致
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes			##I/O线程要为YES
            Slave_SQL_Running: Yes			##SQL线程要为YES
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154			##执行记录要与读取记录保持一致
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 59b0e5de-e412-11e8-ab69-000c293925f5
             Master_Info_File: /opt/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

• 在主（master）中插入数据，并在从（slave）中进行查看

[root@localhost ~]# mysql -uroot -p'123456' -e "select * from student.haha"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| id   | name |
+------+------+
|    1 | n    |
|    2 | x    |
+------+------+