MySQL 主从备份
1. 主从备份
# 配置完成后需要去掉此配置
[mysqld]
skip-grant-tables
2. 主库配置
2.1 配置 my.cnf
[mysqld]
# 数据库唯一ID，主从的标识号绝对不能重复。
server-id = 1
# 开启bin-log，并指定文件目录和文件名前缀
log-bin = mysql-bin
# 需要同步liting数据库。如果是多个同步库，就以此格式另写几行即可。如果不指明对某个具体库同步，就去掉此行，表示同步所有库（除了ignore忽略的库）
binlog-do-db=sync_test
# 不同步mysql系统数据库。
# 如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
#binlog-ignore-db=mysql
# 确保binlog日志写入后与硬盘同步
sync_binlog = 1
# 跳过现有的采用checksum的事件
# mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_checksum = none
# bin-log日志文件格式，设置为MIXED可以防止主键重复。
binlog_format = mixed
注意
在主服务器上最重要的二进制日志设置是 sync_binlog，这使得 MySQL 在每次提交事务的时候把二进制日志的内容同步到磁盘上，即使服务器崩溃也会把事件写入日志中。
sync_binlog 这个参数是对于 MySQL 系统来说是至关重要的，他不仅影响到 Binlog 对 MySQL 所带来的性能损耗，而且还影响到 MySQL 中数据的完整性。对于 sync_binlog 参数的各种设置的说明如下：
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信 息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。sync_binlog=n，当每进行 n 次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。- 在
MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统 Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为"1"的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使系统 Crash，也最多丢失 binlog_cache 中未完成的一个事务，对实际数据没有任何实质性影响。
从以往经验和相关测试来看，对于高并发事务的系统来说，
sync_binlog设置为"0"和设置为"1"的系统写入性能差距可能高达 5 倍甚至更多。
2.2 导出 master 数据库多于 slave 数据库中的数据，然后导入到 slave 数据库中。保证双方再同步环境实现前的数据一致。
-- 导出数据库之前先锁定数据库
-- 数据库制度锁定命令，防止导出数据的时候有数据写入。unlocke tables 命令解除锁定
mysql> flus tables with read lock
-- 导出 master 数据库中需要同步的库
-- 将导出的sql文件上传到 slave 机器上
2.3 设置数据同步权限
-- 创建用户
mysql> create user 'repl'@'localhost' identified by 'repl123';
Query OK, 0 rows affected (0.00 sec)
-- 只允许10.1.15.220使用repl，且密码为"123456"连接主库做数据同步
-- 若要所有网段则设置 repl@'%'，部分网段：repl@'10.1.15.%'
mysql> grant replication slave,replication client on *.* to repl@'10.1.15.15' identified by 'repl123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
-- 刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
-- 提示：权限查看
mysql> show grants;
mysql> show grants for repl@'10.1.15.15';
-- 查看主服务器master状态(**注意File与Position项，从服务器需要这两项参数**)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 1143 | sync_test | | |
+------------------+----------+--------------+------------------+-------------------+
2.4 防火墙开放端口
firewall-cmd --zone=public --add-port=3306/tcp --parmanent
3. 从库配置
3.1 配置 my.cnf
[mysqld]
# 数据库唯一ID，主从的标识号绝对不能重复。
server-id = 2
# 开启bin-log，并指定文件目录和文件名前缀
log-bin = mysql-bin
# 需要同步的数据库名。
# 如果不指明同步哪些库，就去掉这行，表示所有库的同步（除了ignore忽略的库）。
replicate-do-db=sync_test
# 当只针对某些库的某张表进行同步时。如下，只同步 sys_test 库的 test表
#replicate-do-db=sync_test
#replicate-wild-do-table=sync_test.test
# 不同步mysql系统数据库。
# 如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
#replicate-ignore-db=mysql
# 跳过所有错误
slave-skip-errors=all
3.2 导入从 master 中导来的数据
-- 创建空库
mysql> create database sync_test character set utf8 collate utf8_general_ci;
mysql> use sync_test;
-- 导入数据
mysql> source /opt/data.sql;
3.3 配置主从指令
-- 执行同步前，先关闭 slave
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
-- 配置主从同步指 令
mysql> change master to master_host='10.1.15.220',master_user='repl',master_password='repl123',master_log_file='mysql-bin.000008',master_log_pos=1143;
Query OK, 0 rows affected, 2 warnings (0.15 sec)
-- 启动 slave
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
-- 查看 slave 状态，如下，当 IO 和 SQL进程的状态均为 Yes，则表示主从已实现同步
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.15.220
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 1567
Relay_Log_File: DESKTOP-DV7B57O-relay-bin.000002
Relay_Log_Pos: 1143
Relay_Master_Log_File: mysql-bin.000008
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: 1567
Relay_Log_Space: 1352
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: a138dccd-4a09-11ed-9b14-a0b3cce90f03
Master_Info_File: D:\apps\mysql\mysql_data\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)
1 row in set (0.00 sec)
附：设置允许账号远程登录
-- 允许远程登录，解决登录时1130错误
mysql> update user set host='%' where user='userName';
Query OK, 1 row affected (0.03 sec)
-- 查看用户可登录方式
mysql> select user,host from user;
+---------------+-------------+
| user | host |
+---------------+-------------+
| root | % |
| repl | 10.1.15.220 |
| mysql.session | localhost |
| mysql.sys | localhost |
| repl | localhost |
+---------------+-------------+
参考
- 史上最全 MySQL 主从同步与主主同步 - 博客园