docker run -d --net=host --name mysql --restart=always -v /etc/localtime:/etc/localtime:ro -e MYSQL_ROOT_PASSWORD=qianbitou504 -e MYSQL_ALLOW_EMPTY_PASSWORD=no -v /data/file:/data/file/ -v /data/docker/mysql/conf.d:/etc/mysql/conf.d:ro -v /data/db/mysql:/var/lib/mysql mysql:5.6
docker run -d --net=host --name mysql --restart=always -v /etc/localtime:/etc/localtime:ro -e MYSQL_ROOT_PASSWORD=qianbitou504 -e MYSQL_ALLOW_EMPTY_PASSWORD=no -v /data/file:/data/file/ -v /data/docker/mysql/conf.d:/etc/mysql/conf.d:ro -v /data/db/mysql:/var/lib/mysql mysql:5.6
# 将主服务器需要同步的数据库内容进行备份一份，上传到从服务器上，保证始初时两服务器中数据库内容一致。
# 清空两台主机的master和slave日志
reset master;
reset slave;
#主服务器Master配置
---my.cnf
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
max_connections = 500
skip_name_resolve = 1
# 开启事件执行器
event_scheduler=1
server-id = 21
slow_query_log = 1
slow_query_log_file = /data/file/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 0
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log-bin = /data/file/mysql/binlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 1G
max_binlog_size = 1G
expire_logs_days = 14
binlog_format = ROW
binlog_row_image = full
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
max_allowed_packet = 512M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
MySQL主从 （gtid模式）
---
mariadb10.x默认就是支持GTID的复制方式
1.不支持的参数
gtid-mode=on
enforce-gtid-consistency=true
2.修改的参数
slave-parallel-workers参数修改为slave-parallel-threads
3.连接至主服务使用的命令
增加：master_use_gtid= current_pos | slave_pos | no
---
方法一：传统的方式
CHANGE MASTER TO
MASTER_HOST='10.0.22.248',
MASTER_PORT=3306,
MASTER_USER='liuwei',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000024',
MASTER_LOG_POS=875;
方法二：MASTER_USE_GTID的方式
CHANGE MASTER TO
MASTER_HOST='10.0.22.248',
MASTER_PORT=3306,
MASTER_USER='liuwei',
MASTER_PASSWORD='123456',
MASTER_USE_GTID=slave_pos;
---master
gtid-mode = on //开启gtid模式
enforce-gtid-consistency = on //强制gtid一致性，开启后对特定的create table不被支持
binlog-format = row //默认为mixd混合模式，更改成row复制
log-slave-updates = 1 //日志更新打开
skip-slave-start = 1 #跳过slave复制线程（必须从主库开始复制）
show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000014 | 1951 | | | b736875b-097e-11ec-b557
————————————————
---从
skip-slave-start = 1
log-slave-updates = 1
gtid-mode = on
enforce-gtid-consistency = on
---
change master to
-> master_host='192.168.75.160' ,
-> master_user='yang' ,
-> master_password='1234' ,
-> master_auto_position=1;
---双主多从
//主库1
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000014 | 2128
————————————————
主库2
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000002 | 154 |
————————————————
---在从库上配置主库信息
set global master_info_repository='table';
set global relay_log_info_repository='table';
//主库1
change master to
-> master_host='192.168.75.160' ,
-> master_user='yang' ,
-> master_password='1' ,
-> master_log_file='mysql-bin.000014'
-> master_log_pos=2128
-> for channel 'master-1';
//主库2
mysql> change master to
-> master_host='192.168.75.150' ,
-> master_user='ang' ,
-> master_password='1' ,
-> master_log_file='mysql-bin.000002'
-> master_log_pos=154
-> for channel 'master-2'; //创建隧道
mysql> START SLAVE;
//查看
mysql> SHOW SLAVE STATUS\G
---GTID如何跳过事务冲突
1、这个功能主要跳过事务，代替原来的set global sql_slave_skip_counter = 1。
2、由于在这个GTID必须是连续的，正常情况同一个服务器产生的GTID是不会存在空缺的。所以不能简单的skip掉一个事务，只能通过注入空事物的方法替换掉一个实际操作事务。
3、注入空事物的方法：
stop slave;
set gtid_next='xxxxxxx:N';
begin;commit;
set gtid_next='AUTOMAIC';
start slave;
4、这里的xxxxx:N 也就是你的slave sql thread报错的GTID，或者说是你想要跳过的GTID。
---创建同步用户
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'wdqdmm@m';
flush privileges;
---查看状态
SHOW MASTER STATUS;
---锁住表，防止写操作,
flush tables with read lock;
---!完成同步, 最好解锁
unlock tables;
# 从服务器Slave配置
---my.cnf
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
max_connections = 500
skip_name_resolve = 1
# 开启事件执行器
event_scheduler=1
server-id = 22
slow_query_log = 1
slow_query_log_file = /data/file/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 0
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log-bin = /data/file/mysql/binlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 1G
max_binlog_size = 1G
expire_logs_days = 14
binlog_format = mixed
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
max_allowed_packet = 512M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
---用change mster 语句指定同步位置
stop slave; //先停步slave服务线程，这个是很重要的，如果不这样做会造成以下操作不成功。
change master to master_host='59.151.15.36', MASTER_PORT=3306, master_user='slave', master_password='123456', master_log_file='mysql-bin.000016', master_log_pos=107;
注：master_log_file, master_log_pos由主服务器（Master）查出的状态值中确定。也就是刚刚叫注意的。master_log_file对应File, master_log_pos对应Position。Mysql 5.x以上版本已经不支持在配置文件中指定主服务器相关选项。
-遇到的问题，如果按上面步骤之后还出现如下情况, 则要重新设置slave。指令如下
stop slave;
reset slave;
-之后停止slave线程重新开始。成功后，则可以开启slave线程了。
start slave;
---查看从服务器（Slave）状态
show slave status\G
-查看下面两项值均为Yes，即表示设置从服务器成功。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
=============================================
#如何解决MySQL主从同步错误的SQL
stop slave;
set global sql_slave_skip_counter =1; #表示跳过一步错误，后面的数字可变
start slave;
-查看
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# Seconds_Behind_Master: 和主库比同步延迟的秒数
show slave status\G;
从库状态 Seconds_Behind_Master 变小到很小就可以
# Slave_SQL_Running: No mysql同步故障
发现一台MySQL Slave未和主机同步，查看Slave状态：
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
stop slave ;
-跳过错误步数，后面步数可变
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave ;
# MySQL的错误：No query specified
show slave status\G;的时候，多加了一个“;”分号。
# max_binlog_cache_size设置不当
show global status like '%binlog_cache%';
set global max_binlog_cache_size=40*1024*1024*1024;
# 主从时出现如下错误:The slave I/O thread stops because master and slave have equal MySQL server ids
-两边的server_id 是否一样，一样则修改my.cnf设置
show variables like '%server_id%';
SET GLOBAL server_id=2;
-如果一样则删除从数据库的数据目录下的auto.cnf：
show variables like '%uuid%';
-确认主从数据库的数据host是否一样，如果主从数据库的host一样也会报这个错误.
rm -rf /var/lib/mysql/auto.cnf
-他们有不同的作用。当主从同步时如果主从实例的server-uuid相同会报错退出，
-不过我们可以通过设置replicate-same-server-id=1来避免报错（不推荐）
replicate-same-server-id=1
# 去除主从
reset slave all;
