text

Ubuntu14 & MySql5.6 主从数据库同步设置

作者

服务器及环境

主服务器  
Ubuntu 14.04  
Mysql 5.6  
从服务器  
Ubuntu 18.04.5 LTS  
Mysql 5.7.32

主数据库配置

修改配置文件

vi /etc/mysql/my.cnf
#启用二进制日志  
server-id = 205 #内网IP最后一段
log_bin = /var/lib/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog-format = mixed
#这个比较重要，直接影响同步的性能 延时等问题.mysql5.7多源复制参数，5.7之前版本可不加
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#GTID模式
gtid-mode=on
enforce-gtid-consistency

确认主服务器状态

mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.004348 | 41427720 |              |                  |
+------------------+----------+--------------+------------------+

建立内网同步账号

mysql> grant replication slave on *.* to [slave205@192.168.0.242](mailto:slave205@192.168.0.242) identified by 'wu3cyw' ;

备份数据库

#导出所有的表
mysqldump -uroot -p --single-transaction -R -E --all-databases > /tmp/all_database.sql  | gzip > backup_all_database.sql.gz

从数据库配置

导入数据

mysql -uroot -p< all_database.sql

修改配置文件

vi /etc/mysql/mysql.conf.d/mysqld.cnf
server-id = 242
read_only = 1

#需要同步的库，不指定默认同步全部库
#binlog-do-db=db1
#不需要同步的库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

#binlog日志设置
log-slave-updates = ON
slave-parallel-type=LOGICAL_CLOCK
relay_log_recovery=ON
#超时
slave_net_timeout = 30
#复制并发数设置
slave_parallel_workers = 5

#从库复制跳过错误
slave-skip-errors = 1062,1053,1146,1213,1264,1205,1396
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#mysql5.7多源复制必须添加的参数(不加报错)，5.7版本之前不用加
master_info_repository=TABLE
relay_log_info_repository=TABLE
#GTID模式(使用GTID就可以不用记录备份位置点)
gtid-mode=on
enforce-gtid-consistency

#master-info-file = master.info
#relay-log = relay-relay-bin
#relay-log-index = relay-relay-bin.index
#relay-log-info-file=relay-relay-log.info

skip-slave-start #防止复制随着mysql启动而自动启动
slave-skip-errors=all

指定用户，主数据库IP、端口以及开始执行复制的日志文件和位置

mysql> change master to master_host='192.168.0.241',master_user='slave205',master_password='1p72345v', master_log_file='mysql-bin.004348',master_log_pos=41427720;

启动从库同步

mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.241
Master_User: slave205
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.004349
Read_Master_Log_Pos: 4178522
Relay_Log_Pos: 4178719
Relay_Master_Log_File: mysql-bin.004349
Slave_IO_Running: Yes #正常为yes
Slave_SQL_Running: Yes #正常为yes
Exec_Master_Log_Pos: 4197839

新增管理用户

#查询用户
mysql> SELECT user,host,authentication_string,password_last_changed FROM mysql.user;
#新增用户
create user dbuser1 identified by 'passwd';
#授权
grant all privileges on *.* to dbuser1@'%' identified by 'passwd';
#刷新权限
flush privileges;
#查看用户权限
show grants for 'dbuser1';

远程连接查看数据库状态。

发表评论