Linux下MySQL主从复制(Binlog)的部署过程

# yum安装MySQL5.7（默认最新版本）
[root@Mysql-Master01 ~]# wget https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm
[root@Mysql-Master01 ~]# yum localinstall -y mysql57-community-release-el7-10.noarch.rpm
[root@Mysql-Master01 ~]# yum repolist enabled | grep "mysql.*-community.*"
[root@Mysql-Master01 ~]# yum install -y mysql-community-server
--------------------------------------------------------------------------------------------------------------------------
# 安装MySQL5.7.34（指定版本),必须按顺序安装（common-->libs-->client-->server）
# CentOS7版本需要先清理系统默认自带安装了MariaDB。
[root@Mysql-Master01 ~]# ls
mysql-community-client-5.7.34-1.el7.x86_64.rpm  mysql-community-libs-5.7.34-1.el7.x86_64.rpm
mysql-community-common-5.7.34-1.el7.x86_64.rpm  mysql-community-server-5.7.34-1.el7.x86_64.rpm

[root@Mysql-Master01 ~]# rpm -ivh mysql-community-common-5.7.34-1.el7.x86_64.rpm
[root@Mysql-Master01 ~]# rpm -ivh mysql-community-libs-5.7.34-1.el7.x86_64.rpm
[root@Mysql-Master01 ~]# rpm -ivh mysql-community-client-5.7.34-1.el7.x86_64.rpm
[root@Mysql-Master01 ~]# rpm -ivh mysql-community-server-5.7.34-1.el7.x86_64.rpm
==========================================================================================================================
# 启动mysqld
[root@Mysql-Master01 ~]# systemctl start mysqld
[root@Mysql-Master01 ~]# netstat -nutpl | grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      5059/mysqld
[root@Mysql-Master01 ~]# ps -ef | grep mysql
mysql      5059      1  0 14:38 ?        00:00:02 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

# mysql安全初始化
[root@Mysql-Master01 ~]# grep ‘temporary password‘ /var/log/mysqld.log
2021-08-12T02:11:09.461541Z 1 [Note] A temporary password is generated for root@localhost: 2ofTdw8ntD>V

[root@Mysql-Slave01 ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:　　　　# 输入上面的初始密码 2ofTdw8ntD>V
The ‘validate_password‘ plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y　　　　# 是否修改root密码

New password:　　　　　　# 输入符合密码策略的root密码

Re-enter new password:　　　　# 再次输入

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y　　　　# 是否希望继续使用所提供的密码
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y　　　　# 是否删除匿名账号
Success.


Normally, root should only be allowed to connect from
‘localhost‘. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y　　　　# 是否禁止root远程登录
Success.

By default, MySQL comes with a database named ‘test‘ that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y　　　　# 是否删除test库和对test库的访问权限
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y　　　　# 是否刷新授权表使修改生效
Success.

All done!

[root@Mysql-Master01 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.34, for Linux (x86_64) using  EditLine wrapper

# 设置master01的my.cnf(必须在[mysqld]配置区域)
[root@Mysql-Master01 ~]# cat /etc/my.cnf
[mysqld]
......
# 新增以下内容
server-id = 37
log-bin = master-bin
binlog-do-db = mydb
binlog-ignore-db = mydb1
binlog-format = mixed
sync_binlog = 1
expire_logs_days = 30
skip_name_resolve = ON

参数说明：
　　server-id 　　　　master01服务器唯一ID，一般IP最后一段，主从不能重复
　　log-bin 　　　　　开启bin-log，并可指定文件文件目录和前缀
　　binlog-do-db　　 需要同步的数据库；如需同步多个库写多行即可；如不写此行，默认同步所有库
　　binlog-ignore-db 无需同步的数据库；如无需同步多个库写多行即可；也可以在一行，各库名用逗号隔开
　　binlog-format 　　二进制日志格式，有row、statement、mixed三种格式，
　　　　　row指的是把改变的内容复制过去，而不是把命令在从服务器上执行一遍，
　　　　　statement指的是在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。
　　　　　mixed指的是默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制；可防止主键重复。
　　sync_binlog　　　　确保binlog日志写入后与硬盘同步
　　expire_logs_days　自动清理 7 天前的log文件,可根据需要修改
　　skip_name_resolve　跳过反向域名解析

# 重启MySQL
[root@Mysql-Master01 ~]# systemctl restart mysqld

# 测试登录msyql
[root@Mysql-Master01 ~]# mysql -uroot -p
Enter password:

# 创建测试库mydb (对应配置文件my.cnfd的binlog-do-db)
mysql> CREATE DATABASE IF NOT EXISTS mydb;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb;
Database changed
mysql> CREATE TABLE IF NOT EXISTS tb1 (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO mydb.tb1 VALUES(1,"zhangsan"),(2,"lisi");
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mydb.tb1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

# 创建测试库mydb1 (对应配置文件my.cnfd的binlog-ignore-db)
mysql> CREATE DATABASE IF NOT EXISTS mydb1;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS tb2 (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO mydb1.tb2 VALUES(1,"wangwu"),(2,"zhaoliu");
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FRoM mydb1.tb2;
+----+---------+
| id | name    |
+----+---------+
|  1 | wangwu  |
|  2 | zhaoliu |
+----+---------+
2 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mydb1              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> show variables like ‘server_id‘;　　　　# 查看服务器唯一ID
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 37    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like ‘%log_bin%‘;　　　　# 查看是否开启log_bin
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
| log_bin_basename                | /var/lib/mysql/master-bin       |
| log_bin_index                   | /var/lib/mysql/master-bin.index |
| log_bin_trust_function_creators | OFF                             |
| log_bin_use_v1_row_events       | OFF                             |
| sql_log_bin                     | ON                              |
+---------------------------------+---------------------------------+
6 rows in set (0.00 sec)

mysql> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000003 |       154 |
+-------------------+-----------+
3 rows in set (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘backup‘@‘172.16.%.%‘ IDENTIFIED BY ‘Backup@01‘;　　　　# 建立backup账户并授权slave
Query OK, 0 rows affected, 1 warning (0.00 sec)

语句说明：
(1) replication slave为mysql同步的必须权限，此处不要授权all权限
(2) *.* 表示所有库所有表，也可以指定具体的库和表进行复制。例如mydb.tb1中，mydb为库名，tb1为表名
(3) ‘backup‘@‘172.16.%.%‘ backup为同步账号。172.16.%.%为授权主机网段，使用了%表示允许整个172.16.0.0网段可以用backup这个用户访问数据库
(4) identified by ‘Backup@01‘;  Backup@01为密码，实际环境下设置复杂密码

mysql> flush privileges;　　　　# 刷新权限
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user where user=‘backup‘;　　　　# 查看是否存在backup用户
+--------+------------+
| user   | host       |
+--------+------------+
| backup | 172.16.%.% |
+--------+------------+
1 row in set (0.01 sec)

mysql> show grants for backup@‘172.16.%.%‘;　　　　# 查看backup用户授权
+---------------------------------------------------------+
| Grants for backup@172.16.%.%                            |
+---------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO ‘backup‘@‘172.16.%.%‘ |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> flush table with read lock;　　　　# 对主数据库锁表只读，防止导出数据库的时候有数据写入。unlock tables命令解除锁定
Query OK, 0 rows affected (0.00 se

mysql> show variables like ‘%timeout%‘;
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |　　# 自动解锁时间受本参数影响
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| wait_timeout                | 28800    |　　# 自动解锁时间受本参数影响
+-----------------------------+----------+
13 rows in set (0.00 sec)

mysql> show master status;　　　　# 锁表后查看主库状态
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 |      154 | mydb         | mydb1            |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 锁表后，一定要单开一个新的SSH窗口，导出mydb数据库,发送至slave
[root@Mysql-Master01 ~]# mysqldump -uroot mydb -p > /tmp/mydb.sql
Enter password:
[root@Mysql-Master01 ~]# ls /tmp/mydb.sql
/tmp/mydb.sql
[root@Mysql-Master01 ~]# rsync -avz /tmp/mydb.sql root@172.16.70.181:/tmp/
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘172.16.70.181‘ (ECDSA) to the list of known hosts.
root@172.16.70.181‘s password:　　# slave服务器root密码

#导出数据完毕后，解锁主库
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

[root@Mysql-Slave01 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.34, for Linux (x86_64) using  EditLine wrapper

# 设置slave01的my.cnf(必须在[mysqld]配置区域)
[mysqld]
......
# 新增以下内容
server-id = 181
log-bin = slave-bin
replicate-do-db = mydb
replicate-ignore-db = mydb1
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

参数说明：
server-id 　　　　　　slave01服务器唯一ID，一般IP最后一段，主从不能重复
log-bin 　　　　　　　开启bin-log，并可指定文件文件目录和前缀
replicate-do-db　　　需要同步的数据库名。如果不指明同步哪些库，就去掉这行，表示所有库的同步（除了ignore忽略的库）
replicate-ignore-db　　无需同步的数据库
relay-log 　　　　　　　将主服务器上同步日志文件记录到本地
relay-log-index 　　　定义 relay-log 的位置和名称

# 重启MySQL
[root@Mysql-Slave01 ~]# systemctl restart mysqld

# 登录mysql
[root@Mysql-Slave01 ~]# mysql -p
Enter password:

mysql> show variables like ‘server_id‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 181   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show variables like ‘%log_bin%‘;
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/slave-bin       |
| log_bin_index                   | /var/lib/mysql/slave-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

# slave创建mydb空库，否则下面导入数据时会报错说此库不存在
mysql> CREATE DATABASE IF NOT EXISTS mydb;
Query OK, 1 row affected (0.01 sec)

mysql> source /tmp/mydb.sql

# 配置主从同步指令
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> change master to master_host=‘172.16.70.37‘,master_port=3306,master_user=‘backup‘,master_password=‘Backup@01‘,master_log_file=‘master-bin.000003‘,master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

参数说明：（提示：字符串用单引号括起来，数值不用引号，注意内容前后不能有空格。）
　　change master to 
　　master_host=‘172.16.70.37‘　　# master主库IP
　　master_port=3306　　　　　　　　# 数据库端口号　　　　　　　　
　　master_user=‘backup‘　　　　　　# master上创建用于复制的用户
　　master_password=‘Backup@01‘　　# 复制用户的密码
　　master_log_file=‘master-bin.000003‘　　# show master status时查看到的二进制日志文件名称，注意不能多空格
　　master_log_pos=154;　　　　　　# show master status时查看到的二进制日志偏移量，注意不能多空格

# 上述操作的原理实际上是把用户密码等信息写入从库新的master.info文件中
/var/lib/mysql/master.info
[root@Mysql-Slave01 ~]# cat /var/lib/mysql/master.info
25
master-bin.000004
154
172.16.70.37
backup
Backup@01
3306
60
0
.....

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: 172.16.70.37
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: mydb
          Replicate_Ignore_DB: mydb1
           Replicate_Do_Table:
　　　　　　......................
　　　　　Seconds_Behind_Master: 0
　　　　　　......................

如上，当IO和SQL线程的状态均为Yes，则表示主从已实现同步了！(主从同步是否成功，最关键的为下面的3项状态参数)
Slave_IO_Running: Yes,这个时I/O线程状态，I/O线程负责从从库到主库读取binlog日志，并写入从库的中继日志，状态为Yes表示I/O线程工作正常。
Slave_SQL_Running: Yes,这个是SQL线程状态，SQL线程负责读取中继日志（relay-log）中的数据并转换为SQL语句应用到从数据库中，状态为Yes表示I/O线程工作正常。
Seconds_Behind_Master:0,这个是复制过程中从库比主库延迟的秒数，这个参数极度重要，但企业里更准确地判断主从延迟的方法为：在主库写时间戳，然后从库读取时间戳，和当前数据库时间进行比较，从而认定是否延迟。

     测试mysql主从复制效果。

# 先在mastet01上操作，当配置文件my.cnf存在binlog-do-db参数时，必须进入该库创建
mysql> use mydb;
mysql> select database();
+------------+
| database() |
+------------+
| mydb       |
+------------+
1 row in set (0.00 sec)

mysql> insert into mydb.tb1 values(11,"chenqi");
mysql> select * from mydb.tb1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
| 11 | chenqi   |
+----+----------+
4 rows in set (0.00 sec)


#然后slave01上查看是否自动同步
mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> select * from mydb.tb1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
| 11 | chenqi   |
+----+----------+
3 rows in set (0.00 sec)

至此，MySQL主从复制(Binlog)已经实现！

# 关闭firewalld及SELinux
[root@Mysql-Slave02 ~]# systemctl stop firewalld && systemctl disable firewalld
[root@Mysql-Slave02 ~]# setenforce 0
[root@Mysql-Slave02 ~]# sed -i ‘7s/enforcing/disabled/‘ /etc/selinux/config

# 设置相同时区，时间
[root@Mysql-Slave02 ~]# yum install -y ntp
[root@Mysql-Slave02 ~]# systemctl start ntpd && systemctl enable ntpd
[root@Mysql-Slave02 ~]# timedatectl set-timezone Asia/Shanghai

[root@Mysql-Slave02 ~]# hostname -I
172.16.70.182

# yum安装mysql5.7
[root@Mysql-Slave02 ~]# yum localinstall -y mysql57-community-release-el7-10.noarch.rpm
[root@Mysql-Slave02 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.35, for Linux (x86_64) using  EditLine wrapper

启动MySQL
[root@Mysql-Slave02 ~]# systemctl start mysqld && systemctl enable mysqld
[root@Mysql-Slave02 ~]# netstat -nutpl | grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      2613/mysqld
[root@Mysql-Slave02 ~]# ps -ef | grep mysql
mysql      2613      1  0 15:42 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

# MySQL安全初始化
[root@Mysql-Slave02 ~]# grep ‘temporary password‘ /var/log/mysqld.log
2021-08-18T07:42:42.208560Z 1 [Note] A temporary password is generated for root@localhost: hlu?dn31d)N#

[root@Mysql-Slave02 ~]# mysql_secure_installation
......
......跟前面Slave01同样的设置

# Slave02的my.cnf与Slave01的只区别于server-id
[mysqld]
......
server-id = 182
log-bin = slave-bin
replicate-do-db = mydb
replicate-ignore-db = mydb1
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

# 重启MySQL
[root@Mysql-Slave02 ~]# systemctl restart mysqld

# 后面的操作完全与Slave01的一致即可，不再赘述。

1、如何查看主从延迟时间
通过监控 show slave status 命令输出的Seconds_Behind_Master参数的值来判断：
Seconds_Behind_Master=0: 表示主从复制良好；
Seconds_Behind_Master=NULL: 表示io_thread或是sql_thread有任何一个发生故障；
Seconds_Behind_Master=n: 数字越大表示从库延迟越严重。

2、影响延迟因素
这里整理了影响主从复制延迟大致有以下几个原因：
1）主节点如果执行一个很大的事务，那么就会对主从延迟产生较大的影响
2）网络延迟，日志较大，slave数量过多
3）主上多线程写入，从节点只有单线程同步
4）机器性能问题，从节点是否使用了“烂机器”
5）锁冲突问题也可能导致从机的SQL线程执行慢

3、优化主从复制延迟
这个没有说去完全解决，要想解决那么就只能采用同步复制策略。不过，一般不建议使用这种同步模式。显而易见，如果写操作必须等待更新同步完成，肯定会
极大地影响性能，除非你不在乎性能。
1）大事务：将大事务分为小事务，分批更新数据
2）减少Slave的数量，不要超过5个，减少单次事务的大小
3）MySQL 5.7之后，可以使用多线程复制，使用MGR复制架构
4）在磁盘、raid卡、调度策略有问题的情况下可能会出现单个IO延迟很高的情况，可用iostat命令查看DB数据盘的IO情况，再进一步判断
5）针对锁问题可以通过抓去processlist以及查看information_schema下面和锁以及事务相关的表来查看

一、 在已知MYSQL数据库的ROOT用户密码的情况下，修改密码的方法：
在SHELL环境下，使用mysqladmin命令设置：
mysqladmin –u root –p password “新密码”   回车后要求输入旧密码

在mysql>环境中,使用update命令，直接更新mysql库user表的数据：
Update  mysql.user  set  password=password(‘新密码’)  where  user=’root’;
flush   privileges;
注意：mysql语句要以分号”；”结束

在mysql>环境中，使用grant命令，修改root用户的授权权限。
grant  all  on  *.*  to   root@’localhost’  identified  by  ‘新密码’；

二、 如忘记了mysql数据库的ROOT用户的密码，又如何做呢？方法如下：
关闭当前运行的mysqld服务程序：service  mysqld  stop（要先将mysqld添加为系统服务）
使用mysqld_safe脚本以安全模式（不加载授权表）启动mysqld 服务
/usr/local/mysql/bin/mysqld_safe  --skip-grant-table  &

使用空密码的root用户登录数据库，重新设置ROOT用户的密码
＃mysql  -u   root
Mysql> Update  mysql.user  set  password=password(‘新密码’)  where  user=’root’;
Mysql> flush   privileges;