1、修改mysql.service
vim /lib/systemd/system/mysql.service
添加
LimitNOFILE=65535
LimitNPROC=65535
systemctl daemon-reload
2、vim /etc/mysql/mysql.conf.d/mysqld.cnf
注释 bind-address 修改为 0.0.0.0
主数据库增加配置
[mysqld.cnf]
#确保唯一
server-id=99
#作为Master要开启binlog
log-bin=mysql-bin
#binlog format有三种形式:Statement、Mixed、Row,默认设置为mixed
binlog-format=mixed
#需要同步的库,不指定默认同步全部库
#binlog-do-db= edu_info
#不需要同步的库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#这个比较重要,直接影响同步的性能 延时等问题.mysql5.7多源复制参数,5.7之前版本可不加
sync_binlog=1
auto_increment_increment=1
auto_increment_offset=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#GTID模式
gtid-mode=on
enforce-gtid-consistency
grant all privileges on *.* to umast@"%" identified by "123456" with grant option;
flush privileges;
添加同步账号
grant replication slave on *.* to 'slave'@'192.168.10.%' identified by '123456';
flush privileges;
从数据库增加配置
[mysqld.cnf]
#确保唯一
server-id=55
#复制的库,不指定默认备份全部库
#replicate-do-db = edu_info
#不复制的库
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
#超时
slave_net_timeout=30
#复制并发数设置
slave_parallel_workers=16
#从库复制跳过错误
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
从数据库增加主备份,多个增加多次
CHANGE MASTER TO MASTER_HOST='192.168.10.11', MASTER_USER='slave', MASTER_PORT=3306, MASTER_PASSWORD='123456', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master-99'
运行SHOW MASTER STATUS
如出现报错信息:
Slave is not configured or failed to initialize properly.
You must at least set --server-id to enable either a master or a slave.
Additional error messages can be found in the MySQL error log
登录从库,use mysql
1、drop 备份的 ibd表
drop table slave_master_info;
drop table slave_relay_log_info;
drop table slave_worker_info;
drop table innodb_index_stats;
drop table innodb_table_stats;
2、重建(或用建表语句)
source /usr/coolpad/mysql/share/mysql_system_tables.sql
3、重启数据库
# /etc/init.d/mysqld restart
Shutting down MySQL.. [ OK ]
Starting MySQL.. [ OK ]
至此,问题解决,登陆数据库,重新 change master to 即可!