Mysql主从同步

1.主库配置

1.1主库(mysql-master171): ip(192.168.75.171) port(3306)

1.1.1设置server-id值并开启binlog参数

[root@mysql-master171 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql   
socket=/data/mysql/mysql.sock  
server_id=171
log-bin=binlog

1.1.2重启数据库

[root@mysql-master171 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!

1.1.3查看数据库的编号

[root@mysql-master171 ~]# mysql -uroot -p123 -e 'select @@server_id;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         171 |
+-------------+

1.1.4建立同步账号

[root@mysql-master171~]# mysql -uroot -p123
mysql> create user 'rep'@'%' identified  by 'rep123';
mysql> grant  replication slave on *.* to 'rep'@'%';
#查看权限
mysql>  show grants for 'rep'@'%';
+---------------------------------------------+
| Grants for rep@%                            |
+---------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `rep`@`%` |
+---------------------------------------------+

1.1.5锁表只读

#为后面备份准备，注意生产环境要提前申请停机时间；
mysql> flush tables with read lock;
#提示：如果超过设置时间不操作会自动解锁。
mysql> show variables like '%timeout%';
#测试锁表后是否可以创建数据库：
mysql> create database test;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

1.1.6查看主库状态

#查看主库状态，即当前日志文件名和二进制日志偏移量
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000006 |     324 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
#注意：如果想要重置主库，可以使用reset master;生产环境慎用

1.1.7备份主数据库中数据

[root@mysql-master171 ~]# mysqldump -uroot -p123  --all-databases> /backup/mysql_bak.2025-09-03.sql.gz 

1.1.8解锁

mysql> unlock tables;

1.1.9主库备份数据上传到从库

[root@mysql-master171 ~]# scp  /backup/mysql_bak.2025-09-03.sql.gz 192.168.75.172:/backup

2.从库配置

2.1从库(mysql-master172)：ip(192.168.75.172)port(3306)

2.1.1设置server-id值

[root@mysql-master172 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server_id=172

2.1.2重启数据库

[root@mysql-master172 ~]# /etc/init.d/mysqld restart

2.1.2还原从主库备份数据

[root@mysql-mster172 ~]# cd /backup/
[root@mysql-mster172 ~]# gzip  -d mysql_bak.2025-09-03.sql.gz
[root@mysql-mster172 ~]# mysql -uroot -p < mysql_bak.2025-09-03.sql.gz
#检查还原：
[root@mysql-mster172 ~]# mysql -uroot -p -e 'show databases;'

2.1.3设定从库向主库同步

mysql> CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.75.171',
SOURCE_USER='rep',
SOURCE_PASSWORD='rep123',
SOURCE_LOG_FILE='binlog.000006',
SOURCE_LOG_POS=324,
SOURCE_SSL=1; 

2.1.4启动从库同步开关

mysql> start replica;

2.1.5检查状态

mysql> show replica status\G
Slave_IO_Running: Yes    #IO线程是否打开
Slave_SQL_Running: Yes   #SQL线程是否打开

3.MySQL主从复制的状况监测

3.1主库创建一个数据库，从库查看

主库

mysql> create database haha;
Query OK, 1 row affected (0.01 sec)

从库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| haha               |
| information_schema |
| jeams              |
| mysql              |
| performance_schema |
| relay              |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

4.生产环境其他常用设置

4.1配置不记录information_schema   mysql   test  等数据库的操作(增，删，改)的二进制日志文件

4.1.1在主配置文件中添加配置

binlog_ignore_db="information_schema"
binlog_ignore_db="mysql"
binlog_ignore_db="test"

4.1.2重启数据库

[root@mysql-master171 ~]# /etc/init.d/mysqld  restart
[root@mysql-master171 ~]# mysql -uroot -p -e 'show master status\G'
Enter password:
*************************** 1. row ***************************
             File: binlog.000004
         Position: 157
     Binlog_Do_DB:
 Binlog_Ignore_DB: information_schema,mysql,test
Executed_Gtid_Set:

4.2从库开始备份binlog

log-slave-updates  #当从库为其它从库的主库时必须要添加该参数
log_bin = mysql-bin  #设置二进制日志文件的文件前缀为mysql-bin
expire_logs_days = 7  #设置二进制日志文件的保留时间
应用场景：级联复制或从库做数据备份

4.3从库只读

read-only
innodb_read_only = ON或1
注：当用户权限中没有SUPER权限(ALL权限是包括SUPER的)时，从库的read-only生效！