MySql主从同步步骤
简介将主数据库的数据操作，通过日志记录的方式复现到从数据，以达到主从数据一致，保证数据库的高可用原理Master 数据库只要发生变化，立马记录到Binary log 日志文件中Slave数据库启动一个I/O thread连接Master数据库，请求Master变化的二进制日志Slave I/O获取到的二进制日志，保存到自己的Relay log 日志文件中。Slave 有一个 SQL thread定
·
简介
将主数据库的数据操作，通过日志记录的方式复现到从数据，以达到主从数据一致，保证数据库的高可用
原理
- Master 数据库只要发生变化，立马记录到Binary log 日志文件中
- Slave数据库启动一个I/O thread连接Master数据库，请求Master变化的二进制日志
- Slave I/O获取到的二进制日志，保存到自己的Relay log 日志文件中。
- Slave 有一个 SQL thread定时检查Realy log是否变化，变化那么就更新数据
具体实现步骤：
步骤梳理
- 准备至少两台数据库设备
- 主从同步前，将主库的数据备份到从数据库，保证主从一致。因为主从同步实际上是记录的操作日志，并不会对之前的数据做同步
- 开启主数据的日志功能
- 在主数据库创建用户同步数据的user以及对读写操作/ip访问地址的授权信息
- 开启从数据库的日志功能
- 在从数据库上配置同步主数据的账号、访问地址、同步文件、位置等信息
- 开启slave线程执行同步
具体步骤
① 将主数据同步到从数据库
配置清单为：192.168.1.202 为master ,192.168.1.201 为slave
登录到slave
mysqldump -h 192.168.1.202 -uroot -p matai > ./matai.sql
mysqldump命令参考：
1、备份命令
格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql
例如： mysqldump -h 192.168.1.100 -p 3306 -uroot -ppassword --database cmdb > /data/backup/cmdb.sql
2、备份压缩
导出的数据有可能比较大，不好备份到远程，这时候就需要进行压缩
格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 | gzip > 文件名.sql.gz
例如： mysqldump -h192.168.1.100 -p 3306 -uroot -ppassword --database cmdb | gzip > /data/backup/cmdb.sql.gz
3、备份同个库多个表
格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 表1 表2 .... > 文件名.sql
例如 mysqldump -h192.168.1.100 -p3306 -uroot -ppassword cmdb t1 t2 > /data/backup/cmdb_t1_t2.sql
4、同时备份多个库
格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --databases 数据库名1 数据库名2 数据库名3 > 文件名.sql
例如：mysqldump -h192.168.1.100 -uroot -ppassword --databases cmdb bbs blog > /data/backup/mutil_db.sql
5、备份实例上所有的数据库
格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --all-databases > 文件名.sql
例如：mysqldump -h192.168.1.100 -p3306 -uroot -ppassword --all-databases > /data/backup/all_db.sql
6、备份数据出带删除数据库或者表的sql备份
格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --add-drop-table --add-drop-database 数据库名 > 文件名.sql
例如：mysqldump -uroot -ppassword --add-drop-table --add-drop-database cmdb > /data/backup/all_db.sql
7、备份数据库结构，不备份数据
格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --no-data 数据库名1 数据库名2 数据库名3 > 文件名.sql
例如：mysqldump --no-data –databases db1 db2 cmdb > /data/backup/structure.sql
由于mysqldump是数据库级别的操作，因此在导入数据到从数据时候保证有对应的database
在从库上还原主库上的sql文件
mysql -uroot -p matai > ./matai.sql
② 开启主数据库的日志功能
编辑my.cnf （操作系统为centos）
vim /etc/my.cnf
添加如下内容：
server-id = 1 # 节点ID，确保唯一
#log config
log-bin = mysql-bin #开启mysql的binlog日志功能
sync_binlog = 1 #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7 #binlog过期清理时间
max_binlog_size = 100m #binlog每个日志文件大小
binlog_cache_size = 4m #binlog缓存大小
max_binlog_cache_size= 512m #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行
auto-increment-offset = 1 # 自增值的偏移量
auto-increment-increment = 1 # 自增值的自增量
slave-skip-errors = all #跳过从库错误
注意 server-id在主从中的唯一性
关于binlog_format的介绍：
STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
修改完毕后重启mysql使配置文件生效：service mysqld restart
③ 在主库创建负责同步的用户以及授权信息
CREATE USER repl_user IDENTIFIED BY 'repl_passwd';
grant replication slave on *.* to 'repl_user'@'192.168.1.201' identified by 'repl_passwd';
FLUSH PRIVILEGES;
其中repl_user 为用户名，repl_passwd 密码；192.168.1.201 为代表用户repl_user 可以从该地址访问主库同步数据，若是局域网中多台主机需要访问主库，可以使用mysql的通配符**%**，如192.168.1.%代表192.168.1.1 -192.168.1.255都可以访问
④ 开启从数据库的日志功能
编辑从数据库的my.cnf
[root@localhost ~]# vim /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
lower_case_table_names = 1
explicit_defaults_for_timestamp = 0
port = 3306
server_id = 2 #ID全局唯一
default-storage-engine=INNODB
character_set_server=utf8
max_allowed_packet=64M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#log config
log-bin=mysql-bin #binlog
relay-log = mysql-relay-bin #relay-log为主库的操作日志
replicate-wild-ignore-table=mysql.% #忽略mysql自带的表
replicate-wild-ignore-table=test.% #忽略test表
replicate-wild-ignore-table=information_schema.% #忽略information_schema表
可以通过replicate-wild-ignore-table设置不同步的表，也可以通过replicate_do_db指定要同步的表，如果不指定就会同步所有表：
replicate_do_db = test1,test2 #是在slave上配置，指定slave要复制哪个库
replicate-ignore-db=mysql,performance_schema,information_schema #是在slave上配置，指定slave要忽略哪个库
修改完毕后重启mysql使配置文件生效：service mysqld restart
⑤ 绑定从库的同步账号以及地址等信息
首先登录主数据库获取同步文件以及同步的位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1535 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
切换到从库绑定好负责同步的账号、主库IP、文件、同步位置等
CHANGE MASTER TO
MASTER_HOST = '192.168.1.202',
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'repl_passwd',
MASTER_PORT = 3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1535,
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000;
⑥ 启动从库同步线程
切换到从库执行：start slave
通过show slave status\G查询执行状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.202
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1535
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 283
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: mysql.%,test.%,information_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1535
Relay_Log_Space: 456
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: d4d3e43d-bb61-11ea-ae13-00155d0a3208
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 the slave I/O thread to update it
Master_Retry_Count: 60
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
1 row in set (0.00 sec)
⑦ 在主库创建ceshi数据库，观察从库数据库变化
主库：
mysql> create database ceshi charset=UTF8;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ceshi |
| matai |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
从库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ceshi |
| mysql |
| performance_schema |
| test |
+--------------------+
无需安装部署，在线快速体验 Byzer
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)