环境:ubuntu18 mysql5.7
主机:192.168.33.51
从机:192.168.33.52
主库设置
修改mysql配置文件
$ cd /etc/mysql/mysql.conf.d
$ sudo vi mysqld.cnf
[mysqld]
#要想使用二进制binlog,必须开启服务器id,
server_id = 51 #主库服务器实例,该值非0,从库大于主库。
log_bin = /var/log/mysql/mysql-bin-master.log #二进制日志文件
log_bin_index = master-bin.index #二进制日志索引文件
expire_logs_days = 10 #日志过期清理时间
max_binlog_size = 100M #日志文件大小
binlog_cache_size = 4M #日志缓存大小
max_binlog_cache_size = 512M #最大缓存大小
binlog_format = mixed #Row基于行的复制;Statement基于SQL语句的复制;Mixed混合模式
sync_binlog =1 #1表示每次事务提交,都写盘,高并发时影响性能。0不控制。
binlog_do_db = data1 #需要同步的数据库,多个数据库需分行添加
#binlog_ignore_db = mysql #不需要同步的数据库
:wq 保存退出
$sudo service mysql restart #重启启动mysql
$sudo mysql -uroot -p #登录mysql
mysql> create user repl; #创建同步帐号
mysql> grant replication slave on *.* to 'repl'@'192.168.33.52'identified by '12345678'; #设置权限帐号
mysql> flush privileges; #刷新权限表
mysql>show master status \G; //查看二进制日志状态值
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 154
Binlog_Do_DB: data1
Binlog_Ignore_DB: mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)
=====================================愉快的分割线============================
从库设置:
$cd /etc/mysql/mysql.conf.d
$ sudo vi mysqld.cnf
[mysqld]
server_id =52 #从库服务器实例号
log_bin =/var/log/mysql/mysql-bin-slave.log #设置从库二进制日志
replicate_do_db =data1 #添加需要同步的数据库名,如果有多个数据库,分行添加
replicate_ignore_db =mysql #不需要同步的数据库
$sudo service mysql restart #重启mysql
$sudo mysql -uroot -p
mysql>use mysql;
mysql>stop slave;
mysql>change master to master_host='192.168.33.51',master_port=3306,master_user='repl',master_password='12345678',master_log_file='mysql-
bin.000005',master_log_pos=154; #配置从库连接
mysql> start slave; #启动从库同步
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.33.51
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 3685
Relay_Log_File: ubuntu160407-relay-bin.000002
Relay_Log_Pos: 3851
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: data1
Replicate_Ignore_DB: mysql
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: 3685
Relay_Log_Space: 4065
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: 51
Master_UUID: e650d55d-c0ad-11ec-ac4d-54f6c515166e
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 more up
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)