M.7 mysql主从复制搭建
0. env
0.1 功能需求
因业务量慢慢变大，单机MySQL已无法满足现有需求，因此要增加服务器数量实现主从复制，读写分离;
模式：一主一从;
master[db1]:192.168.80.21/Centos7.8.2003/MySQL5.7.33
slave[db2]:192.168.80.22/Centos7.8.2003/MySQL5.7.33
0.2 主要用途
备份
读写分离
高可用和故障切换（需要做HA）
MySQL升级测试
0.3 好处
实现服务器负载均衡
通过复制实现数据的异地备份
提高数据库系统的可用性
1. 主库参数配置[master]
1.1 在主库MySQL配置文件/etc/my.cnf下新增配置
[root@db1 ~]# vim /etc/my.cnf
...
log-bin=master-a-bin
binlog-format=ROW
server-id=1
启用二进制日志log-bin;
binlog格式为ROW;
设置一个全局唯一的server_id为1;
1.2 重启MySQL服务
配置文件更改后重启MYSQL
[root@db1 ~]# systemctl restart mysqld #CentOS7/8
--service mysqld restart #CentOS5/6
1.3 查看主库的状态
[root@db1 ~]# mysql -uroot -p
mysql> show master status;
ERROR 2006 (HY000): MySQL server has gone away #当前错误提示，此时尚未建立备库连接
No connection. Trying to reconnect...
Connection id: 2
Current database: *** NONE ***
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| master-a-bin.000001 | 154 | | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
当前为主库，已经生成ROW文件，位置为154。
1.4 主库新增复制用户
1) 新增一个用户
提前准备好一个有复制权限（replication slave，replication client）的用户，以便从库可以用该用户来连接主库，也可以确保这个用户只有复制的权限。
mysql> CREATE USER 'copy'@'192.168.80.22' IDENTIFIED BY 'Rundba_15';
Query OK, 0 rows affected (0.01 sec)
为该用户授权
mysql> GRANT REPLICATION SLAVE ON *.* TO 'copy'@'192.168.80.22';
Query OK, 0 rows affected (0.00 sec)
刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
2) 备库使用主库新增的用户远程登录验证
[root@db2 ~]# mysql -h192.168.80.21 -ucopy -pRundba_15 -P3306
2. 备库参数配置[slave]
2.1 在备库MySQL配置文件/etc/my.cnf下新增配置
[root@db1 ~]# vim /etc/my.cnf
...
log-bin=master-a-bin
binlog-format=ROW
server-id=2
启用二进制日志log-bin;
binlog格式为ROW;
设置一个全局唯一的server_id为2;
2.2 重启MySQL服务
配置文件更改后重启MYSQL
[root@db2 ~]# systemctl restart mysqld #CentOS7/8
--service mysqld restart #CentOS5/6
2.3 从库上建立主从连接关系
1) 登录从库
[root@db2 ~]# mysql -uroot -pRundba_15
2) 从库指定主库的日志信息和链接信息
连接主服务器，MASTER_LOG_FILE对应主库的file，MASTER_LOG_POS对应主库的Position
mysql> CHANGE MASTER TO MASTER_HOST='192.168.80.21', MASTER_PORT=3306, MASTER_USER='copy', MASTER_PASSWORD='Rundba_15', MASTER_LOG_FILE='master-a-bin.000001',MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
3) 启动slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
4) 查看slave的状态
mysql> show slave status\G #注意没有分号
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.21
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-a-bin.000001
Read_Master_Log_Pos: 771
Relay_Log_File: db2-relay-bin.000002
Relay_Log_Pos: 940
Relay_Master_Log_File: master-a-bin.000001
Slave_IO_Running: Yes #当前Yes，表示正常
Slave_SQL_Running: Yes #当前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: 771
Relay_Log_Space: 1145
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 #当前为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: 9081bc9e-96df-11eb-82c2-000c292d3ca5
Master_Info_File: /var/lib/mysql/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)
观察Slave_IO_Running（IO）、 Slave_SQL_Running（SQL）进程是否为yes，如果为yes说明正常，如果长时间处于"Connecting"状态就检查从库指定的主库的链接信息是否正确；
Seconds_Behind_Master为从库和主库的延迟时间，0表示当前从库和主库的数据是一致的
5) 再次查看主库状态
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| master-a-bin.000001 | 771 | | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3. 验证主备库同步
3.1 主库创建数据库
mysql> CREATE DATABASE cdh DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON cdh.* TO 'cdh'@'192.168.80.%' IDENTIFIED BY 'Rundba_15' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdh |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
3.2 备库验证
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdh |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
cdh数据库已经同步
3.3 备库进一步验证
1) 主库创建表并插入数据
mysql> use cdh;
Database changed
mysql> create table hive(id int,name varchar(20));
Query OK, 0 rows affected (0.09 sec)
mysql> insert into hive(1,'AAA');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,'AAA')' at line 1
mysql> insert into hive values(1,'AAAAA');
Query OK, 1 row affected (0.09 sec)
2) 备库验证表和记录
mysql> use cdh;
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> show tables;
+---------------+
| Tables_in_cdh |
+---------------+
| hive |
+---------------+
1 row in set (0.00 sec)
mysql> select * from hive;
+------+-------+
| id | name |
+------+-------+
| 1 | AAAAA |
+------+-------+
1 row in set (0.00 sec)
备库验证表和记录已经生成。