Mysql主从复制

一、master

vim /etc/mysql/mysql.conf.d/mysqld.cnf

编辑添加一下内容

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
# 可重复填写
binlog_do_db            = table1
binlog_do_db            = tanle2
binlog_do_db            = table3
# 跳过错误，继续执行复制操作(可选)
slave-skip-errors		= 1

保存配置文件，重启mysql服务

service mysqld restart

进入mysql控制台

# 创建账号
create user 'slave'@'内网ip' identified by 'slave';
# 授权
grant replication slave on *.* to 'slave'@'内网ip';
# 修改密码
update user set authentication_string = password('新密码') where `user` = 'slave';
# 刷新权限
flush privileges;
show master status\G;
# 记下主库状态信息中的File和Position，后面会用到

二、slave

vim /etc/mysql/mysql.conf.d/mysqld.cnf

编辑添加一下内容
```shell
server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = user_center
binlog_do_db            = manager_center
binlog_do_db            = customer_center
# 跳过错误，继续执行复制操作(可选)
slave-skip-errors		= 1

保存配置文件，重启mysql服务

service mysqld restart

进入mysql控制台

change master to master_host='内网ip',
master_user='slave',
master_password='slave',
master_log_file='mysql-bin.000100',
master_log_pos=2411;
# 修改设置
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000132', MASTER_LOG_POS=962;
# 查询主从状态
show master status\G;

三、结果

# 查询主从状态
show master status\G;

当看到一下表述时, 表示主从复制部署成功

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

一般失败时, 错误描述字段为Last_IO_Error

# 例如
Last_IO_Error: error connecting to master 'slave@192.168.0.4:3306' - retry-time: 60  retries: 2