Mysql主从复制
在配置Mysql主从方案之前，我们需要了解普通文件的同步方法，如rsync，sersync，inotify，scp，nfs，samba，svn等。
1、NFS网络文件共享可以同步存储数据
2、samba共享数据。
3、定时任务结合rsync，scp
4、ftp数据同步
5、inotify+rsync触发式数据同步
6、ssh scp/rsync
Mysql主从复制
Mysql复制支持单向、双向、实时、异步复制及链式级联，它在复制过程中，可以一台服务器作为主服务器（即Master），而其他多个服务器则可以作为从服务器（Slave）。
复制可以单向如：M=>S，也可以双向如 M<=>M，（也可以组成环状同步）。
如图：
Mysql实现读写分离的方式
1、通过程序实现读写分离，如直接在程序中设置多个连接，当select时候，就连接读数据库的连接，update、insert、delete时就连接写数据库的连接
2、通过软件实现读写分离，如Mysql-proxy、Amoeba
3、开发dbproxy 如：mycat等
mysql主从复制配置
1、准备环境
服务器 192.168.137.10 192.168.137.11
系统环境 centos8
数据库环境 mysql8
2、定义服务器角色
主库（mysql master）192.168.137.10 port：3306 user: root password :Test@123#!
从库（mysql slave01）192.168.137.11 port：3306 user: root password :Test@123#!
3、配置my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[client]
port = 3306
socket=/var/lib/mysql/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user=root
port=3306
validate_password=off
log-bin=/var/lib/mysql/mysql-bin
server-id=1
binlog_format=ROW
#basedir=/app/mysql
#open_files_limit = 1024
#back_log=600
#max_connection=800
#max_connection_errors=3000
#table_cache = 614
#external-looking =FALSE
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid#修改配置替换
egrep "log-bin|server-id" /etc/my.cnf
sed -n /server-id/p /etc/my.cnf
sed -i 's#server-id=1#server-id=2#g' /etc/my.cnf #替换server—id
4、查看log-bin是否生效
mysql -uroot -p123 -S /var/lib/mysql/mysql.sock -e "show variables like 'log_bin';"其他服务器配置同上主要是server-id不同
5、添加账户允许mysql同步
在主库创建同步用户
grant REPLICATION SLAVE,Replication client,reload on *.* to ‘rep’@‘%’ identified by ‘Test@123#!’;
flush privileges;replication slave为mysql同步的必要权限，此次不要授权all
. 表示所有库和表
rep@% rep为同步账户，%及授权主机网段（所有网段）的用户访问
6、修改从库配置文件
server_id = 2
read_only = 1 #表示只读库
replicate-do-db = db_v1 #从库1
replicate-do-db = db_v2 #从库2
log-bin = mall-mysql-bin #日志文件
log-bin-index = mall-mysql-bin.index
relay-log = relay-log
relay_log_index = relay-log.index
log-slave-updates = 1
slave-skip-errors = all7、重启从库mysql服务
执行以下命令
CHANGE MASTER TO
MASTER_HOST='主库ip',
MASTER_PORT=3306,
MASTER_USER='test_slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000012', #上面主库命令查到的文件名
MASTER_LOG_POS=0; #日志同步位置，一般最开始都设置为0然后在从库启动主从同步命令：START SLAVE;
查看同步状态：SHOW SLAVE STATUS;
START SLAVE;
SHOW SLAVE STATUS;备注：
#主库：
grant replication slave on *.* to 'slave'@'%' identified by 'Test@123#!';
mysql> flush privileges;
#备库：
grant replication slave on *.* to 'master'@'%' identified by 'Test@123#!';
mysql> flush privileges;
#主机：
mysql> Change master to master_host='192.168.137.10',
->master_user='master'
->master_password='Test@123#!';
#备库：
mysql> Change master to master_host='192.168.137.10',
->master_user=’slave',
->master_password='Test@123#!';
#主库：
mysql>reset master;
#备库：
mysql>reset slave
mysql>slave start8、主库备份
1、锁表
flush table with read lock;
show master status;
show master logs;
2、备份数据
mysqldump -uroot -p'Test@123#!' -S /var/lib/mysql/mysql.sock -A -B --events|gzip > /opt/rep.sql.gz
mysqldump -uroot -p'Test@123#!' -S /var/lib/mysql/mysql.sock -A -B --events --master-data=2 |gzip > /opt/rep.sql.gz3、解除锁表状态
unlock tables;4、导入数据库
mysql -uroot -p'Test@123#!' -S /var/lib/mysql/mysql.sock < /opt/rep.sql5、mysql自动批量制作主从同步需要的脚本
cat |mysql -uroot -p123 -S /var/lib/mysql/mysql.sock<< EOF
CHANGE MASTER TO
MASTER_HOST='192.168.137.10',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='Test@123#!',
MASTER_LOG_FILE='mysql-bin.000032',
MASTER_LOG_POS=1579
EOF
mysql -uroot -p'Test@123#!' -S /var/lib/mysql/mysql.sock
mysql> 执行脚本
9、开启同步开关
######从库开启开关
start slave
###查看线程
show slave status\G