1、再docker镜像中无法使用vi、vim命令,有一个简单的方法可以直接修改,docker inspect 镜像名。在得出的结果中找到MergedDir,直接cd到指定目录,跟再镜像中操作是一个道理。这里仅仅是操作文件。
2、准备两个数据库镜像
znsx-slave
znsx-master
3、修改master中镜像的my.cnf.目录在/etc/mysql/my.cnf
#mysql的服务id,自己编号,不重复即可
server-id = 1
#report_host = master1
#auto_increment_increment = 2
#auto_increment_offset = 1
#以下两行原本是注释掉,打开即可
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog = 1
#不同步的表,手动添加,也可以改为只同步某一个表
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
4、master创建用户,并授权用户登录许可,登录到mysql中
create user marry@'%' identified by '123456';
grant all on *.* to 'marry'@'%';
5、重启镜像并查看master状态。
注意此处的File、Position非常重要,后面将要使用
注意此处的File、Position非常重要,后面将要使用
注意此处的File、Position非常重要,后面将要使用
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+---------------------------------------------+
| mariadb-bin.000001 | 1256 | | information_schema,mysql,performance_schema |
+--------------------+----------+--------------+---------------------------------------------+
1 row in set (0.000 sec)
6、slave上操作,修改my.cnf。方法同上,修改内容如下
#不可重复
server-id = 2
#report_host = master1
#auto_increment_increment = 2
#auto_increment_offset = 1
#log_bin = /var/log/mysql/mariadb-bin
#log_bin_index = /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog = 1
expire_logs_days = 10
max_binlog_size = 100M
# slaves
#放开以下注释
relay_log = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
relay_log_info_file = /var/log/mysql/relay-bin.info
#log_slave_updates
#设置=1
read_only =1
7、重启slave
8、进入到slave,
CHANGE MASTER TO MASTER_HOST='znsx-master',MASTER_PORT=3306,MASTER_USER='marry',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=631;
MASTER_HOST:要同步的主服务器
MASTER_PORT:主服务器端口
MASTER_USER:同步用户名,前面已经创建
MASTER_PASSWORD:同步密码,放前面已创建
MASTER_LOG_FILE:前面已查看,master中,黑体字部分,对应File
MASTER_LOG_POS:前面已查看,master中,黑体字部分,对应Position
9,、启动同步
start slave
10、重启slave,然后进入到slave,查看slave状态
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: znsx-master
Master_User: marry
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 631
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: mariadb-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:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 631
Relay_Log_Space: 860
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_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
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
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
其中,当Slave_IO_Running和Slave_SQL_Running均为Yes时,即完成同步。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
11、验证,再master上执行sql语句,在slave上查看结果
12、常用语句
查看server_id,如果不重启是不会改变的。
show variables like 'server_id';
如果执行顺序不对,需要停止slave,检查完成之后再start。
stop slave;
额外补充排他表与指定表
master 端:
binlog-do-db 二进制日志记录的数据库(多数据库用逗号,隔开)
binlog-ignore-db 二进制日志中忽略数据库 (多数据库用逗号,隔开)
binlog-do-db=YYY 需要同步的数据库,不在内的不同步。
binlog-ignore-db = MySQL 这是不记录 binlog,来达到从库不同步 MySQL 库,以确保各自权限
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
slave 端
replicate-do-db 设定需要复制的数据库(多数据库使用逗号,隔开)
replicate-ignore-db 设定需要忽略的复制数据库 (多数据库使用逗号,隔开)
replicate-do-table 设定需要复制的表
replicate-ignore-table 设定需要忽略的复制表
replicate-wild-do-table 同 replication-do-table 功能一样,但是可以通配符
replicate-wild-ignore-table 同 replication-ignore-table 功能一样,但是可以加通配符
增加通配符的两个配置
replicate-wild-do-table=db_name.% 只复制哪个库的哪个表
replicate-wild-ignore-table=mysql.% 忽略哪个库的哪个表