MySQL主从配置（两台Linux之间） 简介 主从同步原理 结构图 环境准备 安装部署 测试主从复制
MySQL主从配置（两台Linux之间）
Linux下MySQL数据库的主从同步用来实现读写分离。主数据库进行数据的插入，删除与更新；从数据库专门用来查询操作，缓解数据库的压力。让运行海量数据的时候无论是从速度还是效率上都大大提高，Mysql的主从复制至少是需要两个Mysql的服务，当然Mysql的服务是可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。
主从同步原理
一个异步复制过程，从master复制到slave，由三个线程来完成，其中sql线程和IO线程在slave端，另一个IO线程在master端，要实现MySQL的replication首先需要打开master端的二进制log功能
(1) master 将操作记录到二进制日志(binary log)中；
(2)master有一个I/O线程将二进制日志发送到slave;
(3) slave IO 线程 将master的binary log events读写到它的中继日志(relay log)；
(4) slave SQL进程读取中继日志，将重做记录数据到数据库中。
结构图
环境准备
Linux版本：CentOS Linux release 7.6.1810 (Core)
MySQL安装包：mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar
主数据库：106.53.73.200：3306
从数据库：182.254.184.102：3306
安装部署
上传至Linux服务器的/tmp目录下
安装之前先查看并卸载已安装的mysql，mariadb
1 [root@VM_0_10_centos ~]# rpm -qa | grep mysql 2 [root@VM_0_10_centos ~]# rpm -qa | grep mariadb 3 [root@VM_0_10_centos ~]# rpm -e --nodeps `rpm -qa | grep mysql` 4 [root@VM_0_10_centos ~]# rpm -e --nodeps `rpm -qa | grep mariadb`
在/usr/local目录下创建mysql目录：
1 [root@VM_0_10_centos tmp]# mkdir -p /usr/local/mysql
1.解压
进入上次安装包的/tmp目录下
解压mysql5.7安装包到/usr/local/mysql目录下
1 [root@VM_0_10_centos tmp]# tar -zxvf mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar -C /usr/local/mysql/
2.安装
进入解压目录/usr/local/mysql目录下进行安装（PS: 注意安装顺序）
1 [root@VM_0_10_centos tmp]# cd /usr/local/mysql/
依次执行：
1 [root@VM_0_10_centos mysql]# rpm -ivh mysql-community-common-5.7.27-1.el7.x86_64.rpm 2 [root@VM_0_10_centos mysql]# rpm -ivh mysql-community-libs-5.7.27-1.el7.x86_64.rpm 3 [root@VM_0_10_centos mysql]# rpm -ivh mysql-community-client-5.7.27-1.el7.x86_64.rpm 4 [root@VM_0_10_centos mysql]# rpm -ivh mysql-community-server-5.7.27-1.el7.x86_64.rpm
报错：安装mysql的server服务是报错：缺少依赖包
解决：安装依赖包
1 [root@VM_0_10_centos mysql]# yum -y install numactl
再次运行安装mysql服务即可
1 [root@VM_0_10_centos mysql]# rpm -ivh mysql-community-server-5.7.27-1.el7.x86_64.rpm
4.启动服务
1 [root@VM_0_10_centos mysql]# service mysqld restart
或
[root@VM_0_10_centos mysql]# systemctl restart mysqld
5.修改账户密码
先停止MySQL服务
[root@VM_0_10_centos mysql]# service mysqld stop
编辑my.cnf配置文件
1 [root@VM_0_10_centos mysql]# vi /etc/my.cnf 2 [mysqld] 3 #添加跳过密码验证 4 skip-grant-tables
重启mysql服务
1 [root@VM_0_10_centos mysql]# service mysqld restart
登录数据库修改密码
[root@VM_0_10_centos mysql]# mysql -uroot -p #直接回车即可
mysql> use mysql;
mysql> update user set authentication_string = password('密码') where user = 'root';
mysql> flush privileges;
mysql> grant all privileges on *.* to 'root'@'%' identified by '密码';
mysql> flush privileges;
将my.cnf配置文件中跳过密码啊验证注释，再重启服务
1 [root@VM_0_10_centos mysql]# service mysqld restart
使用账户密码登录mysql
1 [root@VM_0_10_centos mysql]# mysql -uroot -p 2 Enter password:
初始化密码设置成功之后需要对账户进行密码重置（PS:不重置在进行创建数据库会报如下错误）
按照提示重置密码
mysql> alter user 'root'@'localhost' identified by '密码';
PS:如果在重置密码时提示 如下错误：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
这是因为密码不和长度集，设置密码不需要符合长度集
参考网址：https://www.cnblogs.com/ivictor/p/5142809.html
https://blog.csdn.net/brighter_xiao/article/details/51556532
1 mysql> set global validate_password_policy=0; 2 mysql> alter user 'root'@'localhost' identified by '密码';
mysql> flush privileges;
这样就能正常创建数据库了
主从数据库配置
1.操作步骤
1）开启master的二进制日志
2）开启slave的二进制日志
3）将slave指向master
4）开始复制
2.开启master二进制日志
1）编辑mysql配置文件
1 [root@VM_0_10_centos ~]# vi /etc/my.cnf
2）添加二进制日志配置，开启二进制（mysql-bin只是二进制日志名称，可以自行指定）
1 server-id=1 #id是一定要指定的，是唯一的标识（master数据库要比slave数据库的id优先级高才行） 2 log-bin=mysql-bin #开启二进制日志
3）授权
登录数据库
需要给slave数据库配置一个用户/密码的权限
1 mysql> grant replication slave on *.* to 'root'@'slave数据库ip' identified by '密码';
允许某个ip地址的某个用户以某个密码对当前数据库的所有库和表进行复制操作
配置之后需要刷新权限
1 mysql> flush privileges;
上面修改配置文件需重启服务
1 [root@VM_0_10_centos ~]# service mysqld restart
4）查看master的状态
登录数据库
1 mysql> show master status;
file：是日志文件名称
position：日志所在位置
3.开启slave的二进制日志
登录slave服务器
1）配置my.cnf配置文件
1 [root@VM_0_16_centos ~]# vi /etc/my.cnf
2）添加slave二进制日志配置，开启二进制（mysql-bin只是二进制日志名称，可以自行指定）
1 server-id=2 2 log-bin=mysql-bin
注意：每一台指定唯一的一个server-id标识
修改完配置服务需重启服务
1 [root@VM_0_16_centos ~]# service mysqld restart
3）配置slave指向master
登录数据库
1 mysql> change master to 2 -> master_host='master数据库ip', 3 -> master_user='master授权账号', 4 -> master_password='授权密码', 5 -> master_log_file='master日志文件(mysql-bin.000001)', 6 -> master_log_pos=master日志所在位置(154);
master的日志文件名称可以在master数据库使用show master status;查看到
4.开启主从复制
在slave服务器上执行
1 mysql> start slave;
查看slave运行状态
1 mysql> show slave statusG;
可看到如下内容
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 106.53.73.200
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: VM_0_16_centos-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
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: 154
Relay_Log_Space: 536
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: f8100725-c3e5-11e9-ae45-525400da2f1f
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)
ERROR:
No query specified
能查看到这两个为yes则成功
1 Slave_IO_Running: Yes #表示slave的日志读取线程开启 2 Slave_SQL_Running: Yes #表示SQL执行线程开启
测试主从复制
在master数据库创建表
1 mysql> create database test;
2 Query OK, 1 row affected (0.01 sec)
3 mysql> show databases;
4 +--------------------+
5 | Database |
6 +--------------------+
7 | information_schema |
8 | mysql |
9 | performance_schema |
10 | public_thyzs |
11 | sys |
12 | test |
13 +--------------------+
14 6 rows in set (0.00 sec)
15 mysql> use test;
16 Database changed
17 mysql>
18 mysql> CREATE TABLE `t_test` (
19 -> `id` int NOT NULL AUTO_INCREMENT ,
20 -> `content` varchar(20) NULL ,
21 -> PRIMARY KEY (`id`)
22 -> );
23 Query OK, 0 rows affected (0.04 sec)
24
25 mysql> INSERT INTO `t_test` (`content`) VALUES ('test1'),('test2'),('test3'),('test4');
26 Query OK, 4 rows affected (0.03 sec)
27 Records: 4 Duplicates: 0 Warnings: 0
登录从数据库查看
1 mysql> show databases; +--------------------+ 2 | Database | 3 +--------------------+ 4 | information_schema | 5 | cau_thy | 6 | mysql | 7 | performance_schema | 8 | sys | 9 | test | 10 | weixing | 11 +--------------------+ 12 7 rows in set (0.00 sec) 13 14 mysql> use test; 15 Reading table information for completion of table and column names 16 You can turn off this feature to get a quicker startup with -A 17 18 Database changed 19 mysql> 20 mysql> 21 mysql> select * from t_test; 22 +----+---------+ 23 | id | content | 24 +----+---------+ 25 | 1 | test1 | 26 | 2 | test2 | 27 | 3 | test3 | 28 | 4 | test4 | 29 +----+---------+ 30 4 rows in set (0.00 sec)
如果出现不同步情况参考网址：https://blog.csdn.net/heng_ji/article/details/51013710
1 mysql> stop slave; 2 Query OK, 0 rows affected (0.00 sec) 3 4 mysql> set global sql_slave_skip_counter=1; 5 Query OK, 0 rows affected (0.00 sec) 6 7 mysql> start slave; 8 Query OK, 0 rows affected (0.00 sec)
MySQL主从配置（两台Linux之间）
Linux下MySQL数据库的主从同步用来实现读写分离。主数据库进行数据的插入，删除与更新；从数据库专门用来查询操作，缓解数据库的压力。让运行海量数据的时候无论是从速度还是效率上都大大提高，Mysql的主从复制至少是需要两个Mysql的服务，当然Mysql的服务是可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。
主从同步原理
一个异步复制过程，从master复制到slave，由三个线程来完成，其中sql线程和IO线程在slave端，另一个IO线程在master端，要实现MySQL的replication首先需要打开master端的二进制log功能
(1) master 将操作记录到二进制日志(binary log)中；
(2)master有一个I/O线程将二进制日志发送到slave;
(3) slave IO 线程 将master的binary log events读写到它的中继日志(relay log)；
(4) slave SQL进程读取中继日志，将重做记录数据到数据库中。
结构图
环境准备
Linux版本：CentOS Linux release 7.6.1810 (Core)
MySQL安装包：mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar
主数据库：106.53.73.200：3306
从数据库：182.254.184.102：3306
安装部署
上传至Linux服务器的/tmp目录下
安装之前先查看并卸载已安装的mysql，mariadb
1 [root@VM_0_10_centos ~]# rpm -qa | grep mysql 2 [root@VM_0_10_centos ~]# rpm -qa | grep mariadb 3 [root@VM_0_10_centos ~]# rpm -e --nodeps `rpm -qa | grep mysql` 4 [root@VM_0_10_centos ~]# rpm -e --nodeps `rpm -qa | grep mariadb`
在/usr/local目录下创建mysql目录：
1 [root@VM_0_10_centos tmp]# mkdir -p /usr/local/mysql
1.解压
进入上次安装包的/tmp目录下
解压mysql5.7安装包到/usr/local/mysql目录下
1 [root@VM_0_10_centos tmp]# tar -zxvf mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar -C /usr/local/mysql/
2.安装
进入解压目录/usr/local/mysql目录下进行安装（PS: 注意安装顺序）
1 [root@VM_0_10_centos tmp]# cd /usr/local/mysql/
依次执行：
1 [root@VM_0_10_centos mysql]# rpm -ivh mysql-community-common-5.7.27-1.el7.x86_64.rpm 2 [root@VM_0_10_centos mysql]# rpm -ivh mysql-community-libs-5.7.27-1.el7.x86_64.rpm 3 [root@VM_0_10_centos mysql]# rpm -ivh mysql-community-client-5.7.27-1.el7.x86_64.rpm 4 [root@VM_0_10_centos mysql]# rpm -ivh mysql-community-server-5.7.27-1.el7.x86_64.rpm
报错：安装mysql的server服务是报错：缺少依赖包
解决：安装依赖包
1 [root@VM_0_10_centos mysql]# yum -y install numactl
再次运行安装mysql服务即可
1 [root@VM_0_10_centos mysql]# rpm -ivh mysql-community-server-5.7.27-1.el7.x86_64.rpm
4.启动服务
1 [root@VM_0_10_centos mysql]# service mysqld restart
或
[root@VM_0_10_centos mysql]# systemctl restart mysqld
5.修改账户密码
先停止MySQL服务
[root@VM_0_10_centos mysql]# service mysqld stop
编辑my.cnf配置文件
1 [root@VM_0_10_centos mysql]# vi /etc/my.cnf 2 [mysqld] 3 #添加跳过密码验证 4 skip-grant-tables
重启mysql服务
1 [root@VM_0_10_centos mysql]# service mysqld restart
登录数据库修改密码
[root@VM_0_10_centos mysql]# mysql -uroot -p #直接回车即可
mysql> use mysql;
mysql> update user set authentication_string = password('密码') where user = 'root';
mysql> flush privileges;
mysql> grant all privileges on *.* to 'root'@'%' identified by '密码';
mysql> flush privileges;
将my.cnf配置文件中跳过密码啊验证注释，再重启服务
1 [root@VM_0_10_centos mysql]# service mysqld restart
使用账户密码登录mysql
1 [root@VM_0_10_centos mysql]# mysql -uroot -p 2 Enter password:
初始化密码设置成功之后需要对账户进行密码重置（PS:不重置在进行创建数据库会报如下错误）
按照提示重置密码
mysql> alter user 'root'@'localhost' identified by '密码';
PS:如果在重置密码时提示 如下错误：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
这是因为密码不和长度集，设置密码不需要符合长度集
参考网址：https://www.cnblogs.com/ivictor/p/5142809.html
https://blog.csdn.net/brighter_xiao/article/details/51556532
1 mysql> set global validate_password_policy=0; 2 mysql> alter user 'root'@'localhost' identified by '密码';
mysql> flush privileges;
这样就能正常创建数据库了
主从数据库配置
1.操作步骤
1）开启master的二进制日志
2）开启slave的二进制日志
3）将slave指向master
4）开始复制
2.开启master二进制日志
1）编辑mysql配置文件
1 [root@VM_0_10_centos ~]# vi /etc/my.cnf
2）添加二进制日志配置，开启二进制（mysql-bin只是二进制日志名称，可以自行指定）
1 server-id=1 #id是一定要指定的，是唯一的标识（master数据库要比slave数据库的id优先级高才行） 2 log-bin=mysql-bin #开启二进制日志
3）授权
登录数据库
需要给slave数据库配置一个用户/密码的权限
1 mysql> grant replication slave on *.* to 'root'@'slave数据库ip' identified by '密码';
允许某个ip地址的某个用户以某个密码对当前数据库的所有库和表进行复制操作
配置之后需要刷新权限
1 mysql> flush privileges;
上面修改配置文件需重启服务
1 [root@VM_0_10_centos ~]# service mysqld restart
4）查看master的状态
登录数据库
1 mysql> show master status;
file：是日志文件名称
position：日志所在位置
3.开启slave的二进制日志
登录slave服务器
1）配置my.cnf配置文件
1 [root@VM_0_16_centos ~]# vi /etc/my.cnf
2）添加slave二进制日志配置，开启二进制（mysql-bin只是二进制日志名称，可以自行指定）
1 server-id=2 2 log-bin=mysql-bin
注意：每一台指定唯一的一个server-id标识
修改完配置服务需重启服务
1 [root@VM_0_16_centos ~]# service mysqld restart
3）配置slave指向master
登录数据库
1 mysql> change master to 2 -> master_host='master数据库ip', 3 -> master_user='master授权账号', 4 -> master_password='授权密码', 5 -> master_log_file='master日志文件(mysql-bin.000001)', 6 -> master_log_pos=master日志所在位置(154);
master的日志文件名称可以在master数据库使用show master status;查看到
4.开启主从复制
在slave服务器上执行
1 mysql> start slave;
查看slave运行状态
1 mysql> show slave statusG;
可看到如下内容
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 106.53.73.200
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: VM_0_16_centos-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
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: 154
Relay_Log_Space: 536
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: f8100725-c3e5-11e9-ae45-525400da2f1f
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)
ERROR:
No query specified
能查看到这两个为yes则成功
1 Slave_IO_Running: Yes #表示slave的日志读取线程开启 2 Slave_SQL_Running: Yes #表示SQL执行线程开启
测试主从复制
在master数据库创建表
1 mysql> create database test;
2 Query OK, 1 row affected (0.01 sec)
3 mysql> show databases;
4 +--------------------+
5 | Database |
6 +--------------------+
7 | information_schema |
8 | mysql |
9 | performance_schema |
10 | public_thyzs |
11 | sys |
12 | test |
13 +--------------------+
14 6 rows in set (0.00 sec)
15 mysql> use test;
16 Database changed
17 mysql>
18 mysql> CREATE TABLE `t_test` (
19 -> `id` int NOT NULL AUTO_INCREMENT ,
20 -> `content` varchar(20) NULL ,
21 -> PRIMARY KEY (`id`)
22 -> );
23 Query OK, 0 rows affected (0.04 sec)
24
25 mysql> INSERT INTO `t_test` (`content`) VALUES ('test1'),('test2'),('test3'),('test4');
26 Query OK, 4 rows affected (0.03 sec)
27 Records: 4 Duplicates: 0 Warnings: 0
登录从数据库查看
1 mysql> show databases; +--------------------+ 2 | Database | 3 +--------------------+ 4 | information_schema | 5 | cau_thy | 6 | mysql | 7 | performance_schema | 8 | sys | 9 | test | 10 | weixing | 11 +--------------------+ 12 7 rows in set (0.00 sec) 13 14 mysql> use test; 15 Reading table information for completion of table and column names 16 You can turn off this feature to get a quicker startup with -A 17 18 Database changed 19 mysql> 20 mysql> 21 mysql> select * from t_test; 22 +----+---------+ 23 | id | content | 24 +----+---------+ 25 | 1 | test1 | 26 | 2 | test2 | 27 | 3 | test3 | 28 | 4 | test4 | 29 +----+---------+ 30 4 rows in set (0.00 sec)
如果出现不同步情况参考网址：https://blog.csdn.net/heng_ji/article/details/51013710
1 mysql> stop slave; 2 Query OK, 0 rows affected (0.00 sec) 3 4 mysql> set global sql_slave_skip_counter=1; 5 Query OK, 0 rows affected (0.00 sec) 6 7 mysql> start slave; 8 Query OK, 0 rows affected (0.00 sec)