首页 > 代码库 > mysql+AB主从
mysql+AB主从
实验要求环境:
两台Linux服务器
master： 192.168.3.10
slave : 192.168.3.11
前提两台都安装了mysql
一、配置mysql master服务器
1.在/etc/my.cnf 中修改或者增加如下内容：
root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-binlog
log-slave-updates=true
重启mysql服务器
root@mysql-master ~]# service mysqld restart
2.创建Replication用户
root@mysql-master ~]# mysql -u root -p123456
mysql> grant replication slave on *.* to ‘slave‘@‘192.168.3.11‘ identified by ‘123456‘;
mysql> flush privileges; 用于复制型从属服务器（从主服务器中读取二进制日志事件）
3.获得Master DB的相关信息
mysql> show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000001 | 426 | | |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
供Slave DB连接时使用，记录下File和Position的值
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
4.导出数据库并同步给从
root@mysql-master ~]# mysqldump -u root -p123456 --all-databases > /root/allbackup.sql
root@mysql-master ~]# scp /root/allbackup.sql root@192.168.3.11:/root/
二、在 MySQL Slave 上的配置
1.从库连接主库进行测试，如果连接成功说明主库配置成功
[root@mysql-slave ~]# mysql -u slave -p123456 -h 192.168.3.10
2.导入刚刚从主服务器传过来的数据库文件
[root@mysql-slave ~]# mysql -u root -p < /root/allbackup.sql
3.修改MySQL配置文件
[root@mysql-slave ~]# vim /etc/my.cnf
server-id = 2
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
注：主从server-id号不能相同
4.重启数据库
[root@mysql-slave ~]# service mysqld restart
5.在 Slave服务器授权，启动从库，进行主从库数据同步
[root@mysql-slave ~]# mysql -u root -p123456
mysql> CHANGE MASTER TO
-> MASTER_HOST=‘192.168.3.10‘,
-> MASTER_USER=‘slave‘,
-> MASTER_PASSWORD=‘123456‘,
-> MASTER_LOG_FILE=‘mysql-binlog.000001‘,
-> MASTER_LOG_POS=426;
Query OK, 0 rows affected (0.05 sec)
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.10
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-binlog.000001
Read_Master_Log_Pos: 426
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 238
Relay_Master_Log_File: mysql-binlog.000001
Slave_IO_Running: Yes 确保为Yes
Slave_SQL_Running: Yes 确保为Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 426
Relay_Log_Space: 238
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
三、测试是否成功
可以在Master DB创建一个数据库，或者表，到 Slave DB上看，如果配置成功就可以成功同步了
查看主的当前的库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
从slave查看当前存在库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
说明两者中的数据保持了一致性
主库创建库
mysql> create database fuzhenyang;
打开从库，查看是否同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fuzhenyang |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
此时已经看到fuzhenyang库已经同步过来了
实验完毕
本文出自 “空怀感” 博客，转载请与作者联系！
mysql+AB主从