mysql主从配置

简介

• MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

作用

• 实时灾备，用于故障切换
• 读写分离，提供查询服务
• 备份，避免影响业务

环境介绍

• 首先准备三个相同版本的MySQL的服务器,一台为主服务器,其他两台为从服务器(这里用的MySQL5.7二进制安装)
• 主服务器:192.168.149.133
• 从服务器:192.168.149.137
• 从服务器:192.168.149.138
• 若都无数据,无需做备份

主从从复制配置

主配置

在配置之前先检查各个服务器数据库中是否还有其他数据
主:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xkq                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from xkq.student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
+----+------+------+
1 row in set (0.00 sec)

从:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

从:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

• 全库备份
  全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.02 sec)

此锁表的终端必须在备份完成以后才能退出

• 备份主库并将备份文件传送到从库

[root@host ~]# mysqldump -uroot -pxialuo123! --all-databases > /all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host ~]# ls /
all.sql  bin  boot  dev  etc  home  lib  lib64  media  mnt  opt  proc  root  run  sbin  srv  sys  tmp  usr  var
[root@host ~]# scp /all.sql root@192.168.149.137:/
The authenticity of host '192.168.149.137 (192.168.149.137)' can't be established.
ECDSA key fingerprint is SHA256:haL5btdJdtdWwKa/ivfnK/7j3yZaN5WEMeuadalS6VE.
Are you sure you want to continue connecting (yes/no/[fingerprint])? y
Please type 'yes', 'no' or the fingerprint: yes
Warning: Permanently added '192.168.149.137' (ECDSA) to the list of known hosts.
root@192.168.149.137's password: 
all.sql                                                                            100%  853KB  21.4MB/s   00:00 

[root@host ~]# scp /all.sql root@192.168.149.138:/
The authenticity of host '192.168.149.138 (192.168.149.138)' can't be established.
ECDSA key fingerprint is SHA256:Y7YpXS3PlVh0ldCHinvD9HW6ydbncq52nwjGGIGrNmo.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.149.138' (ECDSA) to the list of known hosts.
root@192.168.149.138's password: 
all.sql                                                                            100%  853KB  24.0MB/s   00:00 

解除主库的锁表状态，直接退出交互式界面即可
mysql> quit
Bye

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

从:192.168.149.137
[root@host /]# mysql -uroot -pxialuo123! <all.sql
[root@host /]# mysql -uroot -pxialuo123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xkq                |
+--------------------+
[root@host /]# mysql -uroot -pxialuo123! -e 'select * from xkq.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
+----+------+------+

从:192.168.149.138
[root@host /]# mysql -uroot -pxialuo123! <all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@host /]# mysql -uroot -pxialuo123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xkq                |
+--------------------+
[root@host /]#  mysql -uroot -pxialuo123! -e 'select * from xkq.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
+----+------+------+

在主数据库里创建一个同步账号授权给从数据库使用

从1:
mysql> create user 'roo'@'192.168.149.137' identified by 'xialuo123!';
Query OK, 0 rows affected (0.01 sec)

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

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

从2:
mysql> create user 'ro'@'192.168.149.138' identified by 'xialuo123!';
Query OK, 0 rows affected (0.00 sec)

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

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

• 主数据库配置

[root@host ~]# vim /etc/my.cnf
[mysqld]
datadir = /opt/mysql-data
basedir = /usr/local/mysql
datadir = /opt/mysql-data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql-data/mysql.pid
user = mysql
skip-name-resolve

log-bin=mysql-bin    启用binlog日志
server-id=1       数据库服务器唯一标识符，主库的server-id值必须比从库的小
symbolic-links=0
log-error=/var/log/mysqld.log

重启MySQL
[root@host ~]# systemctl restart mysqld
[root@host ~]# ss -antl
State        Recv-Q       Send-Q              Local Address:Port               Peer Address:Port       Process       
LISTEN       0            32                  192.168.122.1:53                      0.0.0.0:*                        
LISTEN       0            128                       0.0.0.0:22                      0.0.0.0:*                        
LISTEN       0            5                       127.0.0.1:631                     0.0.0.0:*                        
LISTEN       0            128                       0.0.0.0:111                     0.0.0.0:*                        
LISTEN       0            128                          [::]:22                         [::]:*                        
LISTEN       0            5                           [::1]:631                        [::]:*                        
LISTEN       0            80                              *:3306                          *:*                        
LISTEN       0            128                          [::]:111                        [::]:*   

• 查看主库的状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从配置:192.168.149.137

[root@host ~]# vim /etc/my.cnf 
[mysqld]
datadir = /opt/mysql-data
basedir = /usr/local/mysql
datadir = /opt/mysql-data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql-data/mysql.pid
user = mysql

server-id=2
relay-log=mysql-relay-bin
symbolic-links=0
log-error=/var/log/mysqld.log

重启mysql
[root@host ~]# systemctl restart mysqld
[root@host ~]# ss -antl
State        Recv-Q       Send-Q              Local Address:Port               Peer Address:Port       Process       
LISTEN       0            128                       0.0.0.0:22                      0.0.0.0:*                        
LISTEN       0            128                          [::]:22                         [::]:*                        
LISTEN       0            80                              *:3306                          *:*   

配置并启动主从复刻
从1:192.168.149.137
mysql> change master to
    -> master_host='192.168.149.133',
    -> master_user='roo',
    -> master_password='xialuo123!',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=154;
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看从服务器状态
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.149.133
                  Master_User: roo
                  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     这里必须为yes(注意关闭防火墙systemctl stop firewalld.service)
            Slave_SQL_Running: Yes     这里必须为yes
              Replicate_Do_DB: 

从配置:192.168.149.138

从2:192.168.149.138
[root@host ~]# vim /etc/my.cnf
[mysqld]
datadir = /opt/mysql-data
basedir = /usr/local/mysql
datadir = /opt/mysql-data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql-data/mysql.pid
user = mysql

server-id=1
relay-log=mysql-relay-bin
symbolic-links=0
log-error=/var/log/mysqld.log
[root@host ~]# systemctl restart mysqld
[root@host ~]# ss -antl
State        Recv-Q       Send-Q              Local Address:Port               Peer Address:Port       Process       
LISTEN       0            128                       0.0.0.0:111                     0.0.0.0:*                        
LISTEN       0            32                  192.168.122.1:53                      0.0.0.0:*                        
LISTEN       0            128                       0.0.0.0:22                      0.0.0.0:*                        
LISTEN       0            5                       127.0.0.1:631                     0.0.0.0:*                        
LISTEN       0            80                              *:3306                          *:*                        
LISTEN       0            128                          [::]:111                        [::]:*                        
LISTEN       0            128                          [::]:22                         [::]:*                        
LISTEN       0            5                           [::1]:631                        [::]:* 

配置并启动主从复刻
mysql> change master to
    -> master_host='192.168.149.133',
    -> master_user='ro',
    -> master_password='xialuo123!',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看从服务器状态
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.149.133
                  Master_User: roo
                  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     这里必须为yes(注意关闭防火墙systemctl stop firewalld.service)
            Slave_SQL_Running: Yes     这里必须为yes
              Replicate_Do_DB: 

测试

在主的表中进行添加数据

mysql> use xkq;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into student (id,name,age) values (2,'job',30);
Query OK, 1 row affected (0.02 sec)

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
|  2 | job  |   30 |
+----+------+------+
2 rows in set (0.01 sec)

从1:192.168.149.137测试结果

mysql> show tables;
+---------------+
| Tables_in_xkq |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
|  2 | job  |   30 |
+----+------+------+
2 rows in set (0.01 sec)

从2:192.168.149.138测试结果

mysql> show tables;
+---------------+
| Tables_in_xkq |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
|  2 | job  |   30 |
+----+------+------+
2 rows in set (0.02 sec)