MySQL一主多从

MySQL一主多从

Scroll Down

环境配置

IDIP服务
db01192.168.200.65MySQL-master
db02192.168.200.66MySQL-slave
db03192.168.200.67MySQL-slave

修改主库和从库配置文件并重启

Mater主库配置文件

[root@db01 ~]# cat /etc/my.cnf|grep -v "#"
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
port=3306
socket=/application/mysql/tmp/mysql.sock

log-bin=/application/mysql/data/mysql-bin   #打开主库binlog,可自定义路径
server-id=1
binlog_format=MIXED   #开启select也会记录

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

Slave从库配置文件
两Slave从库依次配置,server-id为2,3

[root@db02 data]# cat /etc/my.cnf|grep -v "#"
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
port=3306

log-bin=/application/mysql/data/mysql-bin  #可选择开启或者关闭,不联级的话
server-id=2    #id不能相同
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

查看主库配置是否生效

mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     2767 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在主库创建复制用户rep

mysql> grant replication slave on *.* to 'rep'@'192.168.200.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

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

mysql> select user,host from mysql.user;
+------+---------------+
| user | host          |
+------+---------------+
| dev  | %             |
| root | %             |
| rep  | 192.168.200.% |
+------+---------------+
3 rows in set (0.00 sec)

主库备份文件导入从库

mysql> flush table with read lock;   # 在主库上给所有表加只读锁
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#备份数据
[root@db01 ~]# mysqldump -uroot -p123456 -A -B --events >/opt/rep.sql
[root@db01 ~]# mysqldump -uroot -p123456 -A -B --events --master-data=2 >/opt/rep.sql
#拷贝至从库还原
[root@db01 ~]# rsync -av /opt/rep.sql root@192.168.200.66:/root/
[root@db01 ~]# rsync -av /opt/rep.sql root@192.168.200.66:/root/
[root@db02 data]# mysql -uroot -p123456 < /root/rep.sql
[root@db03 data]# mysql -uroot -p123456 < /root/rep.sql

#打开只读锁
mysql> unlock tables;

两从库配置连接主库信息

#其中host为主库地址 user为上述创建复制用户 MASTER_LOG_FILE和POS主库述查看为准
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.200.65', MASTER_USER = 'rep', MASTER_PASSWORD = '123456', MASTER_PORT = 3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2767; 
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

#执行完毕后相应目录下生成master.info文件
[root@db03 ~]# cat /application/mysql/data/master.info 
23
mysql-bin.000003
2767
192.168.200.65
rep
123456
3306
......

打开从库两个线程

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.65
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 120
               Relay_Log_File: db02-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
#遇到问题两个线程中有一个是NO;是因为server_id相同或者是uuid相同,修改一下就好了,auto.conf文件需删除,根据错误日志检查
[root@db02 data]# ll /application/mysql/data/
总用量 110664
-rw-rw----. 1 mysql mysql       56 12月 31 11:29 auto.cnf
-rw-rw----. 1 mysql mysql    10780 12月 31 11:50 db02.err
-rw-rw----. 1 mysql mysql        6 1月   3 14:18 db02.pid
-rw-rw----. 1 mysql mysql      172 1月   3 14:22 db02-relay-bin.000001
-rw-rw----. 1 mysql mysql      283 1月   3 14:22 db02-relay-bin.000002
-rw-rw----. 1 mysql mysql       48 1月   3 14:22 db02-relay-bin.index
-rw-rw----. 1 mysql mysql        6 12月 31 15:09 db04.pid
-rw-rw----. 1 mysql mysql 12582912 1月   3 14:18 ibdata1
-rw-rw----. 1 mysql mysql 50331648 1月   3 14:18 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 12月 31 11:27 ib_logfile1
-rw-rw----. 1 mysql mysql      126 1月   3 14:22 master.info
drwx------. 2 mysql mysql     8192 12月 31 11:27 mysql
-rw-rw----. 1 mysql mysql      199 1月   3 14:22 mysql-bin.000001
-rw-rw----. 1 mysql mysql       41 1月   3 14:18 mysql-bin.index
drwx------. 2 mysql mysql     4096 12月 31 11:27 performance_schema
-rw-rw----. 1 mysql mysql       57 1月   3 14:22 relay-log.info
drwxr-xr-x. 2 mysql mysql       20 12月 31 11:22 test

检查主从复制是否一致

#主库db01添加新表
mysql> create database kiwi;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kiwi               |
| lw                 |
| mysql              |
| oldboy             |
| oldgril            |
| ops                |
| performance_schema |
| test               |
+--------------------+
9 rows in set (0.00 sec)


#从库db02查看是否复制
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kiwi               |
| lw                 |
| mysql              |
| oldboy             |
| oldgril            |
| ops                |
| performance_schema |
| test               |
+--------------------+
9 rows in set (0.00 sec)

##从库db03查看是否复制
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kiwi               |
| lw                 |
| mysql              |
| oldboy             |
| oldgril            |
| ops                |
| performance_schema |
| test               |
+--------------------+
9 rows in set (0.00 sec)