MYSQL主从库搭建-MySQL5.7

1. 环境说明

测试环境配置信息如下

实现原理

主从复制的原理：

分为同步复制和异步复制，实际复制架构中大部分为异步复制。 复制的基本过程如下：

1).Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；

2).Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave 的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置；  

3).Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”；  

4).Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

1. 配置主从复制

主节点配置：

1. 禁用主节点防火墙

[root@localhost ~]# firewall-cmd --state #查询防火墙状态

running

[root@localhost ~]# systemctl stop firewalld #关闭防火墙

[root@localhost ~]# systemctl disable firewalld #禁用防火墙

Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.

Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

1. 修改主节点主机名称

[root@localhost ~]# hostname #查询mysql服务器主机名

wxc-ecs-prod-db-zyzy-mysql-0001.novalocal

[root@localhost ~]# hostnamectl --static set-hostname wxc-ecs-prod-db-zyzy-mysql-0001.cicd_mysql_master #设置mysql服务器主机名

[root@localhost ~]# reboot #重启服务器

1. 查询主节点数据库状态

[root@localhost ~]# systemctl status mysqld #查询数据库状态

● mysqld.service - MySQL Server 3306

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)

Active: inactive (dead)

Jan 12 08:58:55 wxc-ecs-prod-db-zyzy-mysql-0001.novalocal systemd[1]: Starting MySQL Server 3306...

Jan 12 09:03:44 wxc-ecs-prod-db-zyzy-mysql-0001.novalocal systemd[1]: Stopped MySQL Server 3306.

[root@localhost ~]# systemctl start mysqld #启动mysql数据库

1. 修改主节点my.cnf配置

[root@localhost ~]# vi /u01/db/mysql/3306/cnf/my.cnf #禁用防火墙

[mysqld]

server-id=251330601 #设置数据库唯一的id(服务器IP加编号)，默认是1，slave设置102

1. 修改主节点mysql数据库root密码

[root@localhost ~]# mysql -S /tmp/mysql_3306.sock -uroot -p'rootxxx' #以管理员身份进入mysql数据库

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.36-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> alter user root@'localhost' identified by "rootxxxbb";#修改数据库root密码

Query OK, 0 rows affected (0.00 sec)

1. 在主节点上创建账户并授权slave

mysql> create user 'repl'@'x.x.x.252'identified by "repl_2024bb";#创建同步账号密码

Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'repl'@'x.x.x.252' identified by "repl_2024bb";#为从库分配主从复制权限

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;#mysql用户数据和权限修改后，希望不重启数据库情况下直接生效

Query OK, 0 rows affected (0.00 sec)

mysql> quit #退出mysql数据库

Bye

1. 重启主节点上数据库

[root@localhost ~]# service mysqld restart #重启数据库

1. 查看主节点master状态数据库

[root@localhost ~]# mysql -S /tmp/mysql_3306.sock -uroot -p'rootxxxbb' #以管理员身份进入mysql数据库

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.36-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> show master status; #查看master状态

+------------------+----------+--------------+------------------+------------------------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+------------------------------------------+

| mysql-bin.000011 | 194 | | | aa67eec0-930d-11ec-b954-fa163e157f0c:1-7 |

+------------------+----------+--------------+------------------+------------------------------------------+

1 row in set (0.00 sec)

mysql> quit #退出mysql数据库

从节点配置：

1. 禁用从节点防火墙

[root@localhost ~]# firewall-cmd --state #查询防火墙状态

running

[root@localhost ~]# systemctl stop firewalld #关闭防火墙

[root@localhost ~]# systemctl disable firewalld #禁用防火墙

Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.

Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

1. 修改从节点主机名称

[root@localhost ~]# hostname #查询mysql服务器主机名

wxc-ecs-prod-db-zyzy-mysql-0002.novalocal

[root@localhost ~]# hostnamectl --static set-hostname wxc-ecs-prod-db-zyzy-mysql-0002.cicd_mysql_slave #设置mysql服务器主机名

[root@localhost ~]# hostname #查询mysql服务器主机名

wxc-ecs-prod-db-zyzy-mysql-0002.cicd_mysql_slave

[root@localhost ~]# reboot #重启服务器

1. 查询从节点数据库状态

[root@localhost ~]# service mysqld status #查询数据库状态

Redirecting to /bin/systemctl status mysqld.service

● mysqld.service - MySQL Server 3306

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)

Active: inactive (dead).

[root@localhost ~]# service mysqld start #启动mysql数据库

Redirecting to /bin/systemctl start mysqld.service

[root@localhost ~]# service mysqld status #查询数据库状态

Redirecting to /bin/systemctl status mysqld.service

● mysqld.service - MySQL Server 3306

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)

Active: activating (start) since Fri 2024-01-12 14:30:28 CST; 26s ago

Control: 1608 (mysqld)

CGroup: /system.slice/mysqld.service

└─1608 /u01/app/mysql/bin/mysqld --defaults-file=/u01/db/mysql/3306/cnf/my.cnf --user=mysql

Jan 12 14:30:28 wxc-ecs-prod-db-zyzy-mysql-0002.cicd_mysql_slave systemd[1]: Starting MySQL Server 3306...

1. 修改从节点my.cnf配置

[root@localhost ~]# vi /u01/db/mysql/3306/cnf/my.cnf #禁用防火墙

[mysqld]

server-id=252330602 #设置数据库唯一的id(服务器IP加编号)，默认是1，slave设置102

read-only=1 #是否只读,1 代表只读,0 代表读写

1. 重启从节点数据库

[root@localhost ~]# systemctl restart mysqld #重启mysql数据库

1. 修改从节点mysql数据库root密码

[root@localhost ~]# mysql -S /tmp/mysql_3306.sock -uroot -p'rootxxx' #以管理员身份进入mysql数据库

mmysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.36-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> alter user root@'localhost' identified by "rootxxxbb";#修改数据库root密码

Query OK, 0 rows affected (0.00 sec)

mysql> quit; #退出mysql数据库

[root@localhost ~]# mysql -S /tmp/mysql_3306.sock -uroot -p'rootxxxbb' #以管理员身份进入mysql数据库

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 20

Server version: 5.7.36-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

1. 配置slave

mysql> change master to master_host='x.x.x.251', master_user='repl', master_password='repl_2024bb', master_log_file='mysql-bin.000008', master_log_pos=194; #如果mysgl是 8.0.23之前的版本，执行如下SQL:CHANGE MASTER TO MASTER_HOST='主库IP地址' MASTER_USER='连接主库的用户名', MASTER_PASSWORD='连接主库的密码', MASTER_LOG_FILE='binlog日志文件名', MASTER_LOG_POS=binlog日志文件位置;注：master_log_file和master_log_pos分别对应上一步查到的file和Position

Query OK, 0 rows affected, 2 warnings (0.02 sec)

1. 启动slave同步进展并查看状态

mysql> start slave;#启动slave同步进程

Query OK, 0 rows affected (0.07 sec)

mysql> show slave status\G;#查看slave同步进程状态，当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: x.x.x.251

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000011

Read_Master_Log_Pos: 194

Relay_Log_File: relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000011

Slave_IO_Running: No

Slave_SQL_Running: 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: 194

Relay_Log_Space: 154

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: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 1593

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 251330601

Master_UUID:

Master_Info_File: mysql.slave_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: 240112 15:13:19

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: aa67eec0-930d-11ec-b954-fa163e157f0c:1-4

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:

No query specified

1. 解决同步状态错误

原因：由于两个主机是从同一个母机克隆下来，所以mysql环境配置相同，即主从mysql的UUID相同，然而在配置主从模式下要求UUID不能相同，所以出现此问题。

解决：将slave虚拟机上的UUID的配置文件(auto.cnf)删除然后重启mysql服务即可

①．查找auto.cnf文件

[root@localhost ~]# find / -name auto.cnf #查找auto.cnf文件

/u01/db/mysql/3306/data/auto.cnf

/u02/db/mysql/3307/data/auto.cnf

②．删除slave虚拟机上的UUID配置文件

[root@localhost ~]# rm -rf /u01/db/mysql/3306/data/auto.cnf #删除文件

[root@localhost ~]# rm -rf /u02/db/mysql/3307/data/auto.cnf

③．重启mysql

[root@localhost ~]# service mysqld restart #重启数据库文件

④．查看slave同步进程状态

[root@localhost ~]# mysql -S /tmp/mysql_3306.sock -uroot -p'rootxxxbb' #以管理员身份进入mysql数据库

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 20

Server version: 5.7.36-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> show slave status\G;#查看slave同步进程状态，当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: x.x.x.251

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000011

Read_Master_Log_Pos: 194

Relay_Log_File: relay-bin.000004

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000011

Slave_IO_Running: Yes

Slave_SQL_Running: 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: 194

Relay_Log_Space: 521

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: 251330601

Master_UUID: aa67eec0-930d-11ec-b954-fa163e157f0c

Master_Info_File: mysql.slave_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: aa67eec0-930d-11ec-b954-fa163e157f0c:1-4

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> quit #退出mysql数据库

1. ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size

① 先查询Mysql数据库排序内存

mysql> show variables like '%sort_buffer_size%'; #查询数据库排序内存

+-------------------------+-----------+

| Variable_name | Value |

+-------------------------+-----------+

| innodb_sort_buffer_size | 1048576 |

| myisam_sort_buffer_size | 134217728 |

| sort_buffer_size | 131072 |

+-------------------------+-----------+

3 rows in set (0.00 sec)

重点关注sort_buffer_size大小，如果排序内存过小，可考虑适当增加排序内存，反之需要检查排序

mysql> set global sort_buffer_size = 262144; #查询数据库排序内存

mysql> set global sort_buffer_size = 256M; #查询数据库排序内存

② 永久修改

[root@localhost ~]# vi /u01/db/mysql/3306/cnf/my.cnf #编辑my.cnf参数

1. 主从测试

主节点操作：

1. 主节点上创建数据库表

mysql> show databases; #查询mysql数据库

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

mysql> use mysql; #切换mysql数据库

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> create table test (id int(15),name varchar(300),address1 varchar(300),address2 varchar(300),address3 varchar(300),address4 varchar(300),address5 varchar(300),address6 varchar(300),address7 varchar(300),address8 varchar(300),address9 varchar(300),address10 varchar(300),year date); #创建数据库表test

Query OK, 0 rows affected (0.01 sec)

1. 主节点上创建存储过程

mysql> DROP PROCEDURE IF EXISTS test;

Query OK, 0 rows affected (0.00 sec)

mysql> DROP PROCEDURE IF EXISTS test;

delimiter #

create procedure test()

begin

declare i int default 0;

while i < 20000 do

insert into test(id,name,address1,address2,address3,address4,address5,address6,address7,address8,address9,address10,year) values(i,'小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明小明','深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳','深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳','深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳','深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳','深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳','深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳','深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳','深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳','深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳','深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳深圳','1999-05-21');

set i = i + 1;

end while;

end #

Query OK, 0 rows affected (0.00 sec)

1. 主节点上调用存储过程

mysql> delimiter ;

mysql> call test();

Query OK, 1 row affected (56.27 sec)

从节点上查询结果：

1. 从节点上查询结果

mysql> show databases; #查询mysql数据库

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

mysql> use mysql; #切换mysql数据库

Database changed

mysql> select count(*) from test;

+----------+

| count(*) |

+----------+

| 20000 |

+----------+

1 row in set (0.01 sec)

1. 主节点上删除数据库表和存储过程

mysql> show tables; #查询数据库表

+---------------------------+

| Tables_in_mysql |

+---------------------------+

| columns_priv |

| db |

| engine_cost |

| event |

| func |

| general_log |

| gtid_executed |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| innodb_index_stats |

| innodb_table_stats |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| server_cost |

| servers |

| slave_master_info |

| slave_relay_log_info |

| slave_worker_info |

| slow_log |

| tables_priv |

| test |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+---------------------------+

32 rows in set (0.01 sec)

mysql> drop table test; #删除数据库表

Query OK, 0 rows affected (0.01 sec)

mysql> DROP PROCEDURE IF EXISTS test; #删除存储过程

Query OK, 0 rows affected (0.00 sec)