MySQL一主多从
环境配置
| ID | IP | 服务 |
|---|---|---|
| db01 | 192.168.200.65 | MySQL-master |
| db02 | 192.168.200.66 | MySQL-slave |
| db03 | 192.168.200.67 | MySQL-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)
版权声明：
本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自
爱吃可爱多！
喜欢就支持一下吧
打赏
微信
支付宝
微信
支付宝