https://hub.docker.com/_/mysql
docker run -d -p 3306:3306 --name mariadb --restart=always -e TZ='Asia/Shanghai' --ulimit nofile=262144:262144 -v /etc/localtime:/etc/localtime:ro -e MYSQL_ROOT_PASSWORD='wdqdmm@r' -e MYSQL_ALLOW_EMPTY_PASSWORD='no' -v /data/file:/data/file/ -v /data/site/docker/data/mysql8:/var/lib/mysql mysql:8.0-debian --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --default-authentication-plugin=mysql_native_password --log_bin='ON' --binlog_format='row' --binlog_row_image=full --gtid_mode='ON' --enforce_gtid_consistency='ON' --log_slave_updates='ON' --skip_slave_start='OFF' --log_bin_use_v1_row_events='OFF' --max_allowed_packet='800M' --default_storage_engine='InnoDB' --character-set-client-handshake='FALSE' --character-set-server='utf8mb4' --init_connect='SET NAMES utf8mb4' --max_connections='10000' --wait_timeout='600' --interactive_timeout='600' --bind-address='0.0.0.0' --slow_query_log='ON' --slow_launch_time='10' --general_log='ON' --server_id='11'
---异常
--lower_case_table_names='1'
docker run -d -p 3316:3306 --name mariadbtest --restart=always -e MYSQL_ROOT_PASSWORD='wdqdmm@r' -e MYSQL_ALLOW_EMPTY_PASSWORD='no' -v /data/file:/data/file/ mysql:8.0-debian --default-authentication-plugin=mysql_native_password --log_bin='ON' --binlog_format='row' --binlog_row_image=full --gtid_mode='ON' --enforce_gtid_consistency='ON' --log_slave_updates='ON' --skip_slave_start='OFF' --log_bin_use_v1_row_events='OFF' --max_allowed_packet='800M' --default_storage_engine='InnoDB' --character-set-client-handshake='FALSE' --character-set-server='utf8mb4' --init_connect='SET NAMES utf8mb4' --max_connections='10000' --wait_timeout='600' --interactive_timeout='600' --bind-address='0.0.0.0' --slow_query_log='ON' --slow_launch_time='10' --general_log='ON' --server_id='11'
docker exec -it mariadb /bin/bash
# 将主服务器需要同步的数据库内容进行备份一份，上传到从服务器上，保证始初时两服务器中数据库内容一致。
# 清空两台主机的master和slave日志
reset master;
reset slave;
#主服务器Master配置
---my.cnf
[mysqld]
skip-host-cache
skip-name-resolve
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#secure-file-priv=
secure-file-priv= NULL
bind-address = '0.0.0.0'
#skip-grant-tables
max_connections = '10000'
max_allowed_packet = '500M'
wait_timeout= '600'
interactive_timeout = '600'
server-id = '11'
#是否对sql语句大小写敏感，1表示不敏感
lower_case_table_names = '1'
log_error = '/var/log/mysql/error.log'
slow_query_log = 'ON'
slow_query_log_file = '/var/log/mysql/mysql-slow.log'
slow_launch_time = '10'
general_log = 'ON'
general_log_file = '/var/log/mysql/mysql.log'
#
default_storage_engine = 'InnoDB'
character-set-client-handshake='FALSE'
character-set-server='utf8mb4'
collation-server='utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'
log_bin = 'ON'
binlog_format = 'row'
binlog_row_image = full
gtid_mode = 'ON'
enforce_gtid_consistency = 'ON'
log-slave-updates = 'ON'
skip-slave-start = 'OFF'
log_bin_use_v1_row_events = 'OFF'
default_authentication_plugin = 'mysql_native_password'
# Custom config should go here
!includedir /etc/mysql/conf.d/
MySQL主从 （gtid模式）
---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;
