主从复制+读写分离(双主多从)

一、主从复制: (在主库无数据的情况下)
1、4台机器，3台安装好数据库,一台mycat等待
2、做好域名解析
3、进入数据库，创建远程同步用户[master]
mysql> grant REPLICATION SLAVE on *.* to 'slave'@'%' identified by 'Chen@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4、主库开启binlog日志，查看位置点[master]
vim /etc/my.cnf
server-id=1
log-bin=/var/lib/mysql/mysql-bin

systemctl restart mysqld

# 进入数据库
show master status\G
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001  # 日志文件
         Position: 154   # 位置点
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
5、[root@matser1 ~]# systemctl restart mysqld

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

6、master2从库操作：[从库]
[root@matser2 ~]# vim /etc/my.cnf
server-id=2
[root@matser2 ~]# systemctl restart mysqld
[root@matser2 ~]# mysql -p'Chen@123'
mysql> ? change master to
mysql> edit
CHANGE MASTER TO
  MASTER_HOST='master1',
  MASTER_USER='slave',
  MASTER_PASSWORD='Chen@123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;
 mysql>;执行
 mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G  #看状态，两个yes

Seconds_Behind_Master: 0 #如果状态为0说明没有任何延迟,当业务特别繁忙的时候会出现延迟

测试:
[master1]
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
[master2]
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

二、主从复制:  (有数据)
#在主库有数据的情况下,一定要保证两台数据库的数据一致，再做主从
先做初始化： 全部  或做之前装好做个快照直接恢复快照
systemctl stop mysqld  [ALL]
rm -rf /var/lib/mysql/*  [ALL]
systemctl start mysqld [ALL]
1、导入一些数据
[root@matser1 ~]# ls
anaconda-ks.cfg  test_db-master.zip
[root@matser1 ~]# unzip test_db-maste