MySQL Replicating Data 流程如下图所示:
一、MySQL主从复制
1: 配置主服务器master,编辑MySQL的配置文件/etc/my.cnf添加如下内容 [mysqld] log-bin=master-bin log-bin-index=master-bin.index server-id=10
mysql>GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY '123456';
mysql>FLUSH PRIVILEGES;
配置从服务器slave,编辑MySQL的配置文件/etc/my.cnf添加如下内容 [mysqld] relay-log=slave-relay-bin relay-log-index=slave-relay-bin.index server-id=20
mysql>CHANGE MASTER TO >MASTER_USER='repl', >MASTER_PASSWORD='123456', >MASTER_HOST='192.168.0.68', >MASTER_PORT=3306;
mysql>START SLAVE;
查看从服务器状态: SHOW SLAVE STATUS\G;一切正常
2:如果从服务器不是一开始就主从的连接状态,而是从半道上开始进入连接状态,这个时候我们一定要记下主服务器日志的起始点,如: 配置主服务器master [mysqld] log-bin=master-bin log-bin-index=master-bin.index server-id=10
mysql>GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY '123456'; mysql>FLUSH PRIVILEGES;
配置从服务器slave [mysqld] relay-log=slave-relay-bin relay-log-index=slave-relay-bin.index server-id=20
配置文件完成以后我们要做以下内容:
(1)我们可以把主服务器上的数据先备份下来, mysql>FLUSH TABLES WITH READ LOCK; mysql>SHOW MASTER STATUS; 此处一定要记下主服务器上二进制文件的起始位置,如master-bin.000006 213
(2)备份数据库: #mysqldump -uroot -p --events --triggers --routines >/tmp/db1.sql
(3)备份完成一定要记得解表: mysql>UNLOCK TABLES;
(4)我们要将主服务器备份出来的数据库导入到从服务器中: mysql>CREATE DATABASE db1;
#mysql -uroot -p db1 </tmp/db1.sql
(5)导入完成后我们就可以进入数据库: mysql>CHANGE MASTER TO >MASTER_USER='repl', >MASTER_PASSWORD='123456', >MASTER_HOST='192.168.0.68', >MASTER_PORT=3306; >MASTER_LOG_FILE='master-bin.000006', >MASTER_LOG_POS=213;
mysql>START SLAVE;
这时主从已经进入下常工作状态,并且数据可以保持一至了
3:MySQLt的主从复制之间实现SSL 服务器端配置 [mysqld] log-bin=master-bin log-bin-index=master-bin.index server-id=10 ssl(启动SSL) ssl-ca=/etc/mysql/certs/cacert.pem(跟据实际情况来定) ssl-cert=/etc/mysql/certs/master.crt ssl-key=/etc/mysql/certs/master.key datadir=/mydata(数据的存放目录)
mysql>GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repl@'slave.a.com' IDENTIFIED BY '123456' REQUIRE SSL;
mysql>FLUSH PRIVILEGES;
从服务器slave配置 [mysqld] relay-log=slave-relay-bin relay-log-index=slave-bin.index server-id=20 ssl(启动SSL)
(注:这里要配置好自己的DNS指向或hosts文件,这里需要名称解析)
mysql>CHANGE MASTER TO >MASTER_USER='repl', >MASTER_PASSWORD='123456', >MASTER_HOST='master.a.com', >MASTER_PORT=3306, >MASTER_SSL=1, >MASTER_SSL_CA='/etc/mysql/certs/cacert.pem', >MASTER_SSL_CERT='/etc/mysql/certs/slave.crt', >MASTER_SSL_KEY='/etc/mysql/certs/slave.key';
mysql>START SLAVE; mysql>SHOW SLAVE STATUS\G;(查看SLAVE状态)
二、MySQL的主主复制
两个master server之间的配置如下: [mysql] log-bin=master-bin log-bin-index=master-bin-index auto-increment-increment=2 auto-increment-offset=1 server-id=10
[mysqld] log-bin=master-bin log-bin-index=master-bin.index auto-increment-increment=2 auto-increment-offset=2 server-id=20
启动数据库:
1.我们需要备份当前的数据库,为了在对方数据库上的应用,以节省大量的时间 mysql>FLUSH TABLES WITH READ LOCK;
mysql> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000001 | 106 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
#mysqldump -uroot --triggers --events --routines db1 >/tmp/db1.sql mysql>UNLOCK TABLES;
2.将备份好的数据库传送到对方的服务器上 mysql>CREATE DATABASE db1 #mysql -uroot db1 </root/db1.sql(导入数据库)
mysql>FLUSH TABLES WITH READ LOCK;
mysql> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000001 | 106 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
同上:
接下来要在两服务器上实现:
mysql> CHANGE MASTER TO -> MASTER_USER='db2', -> MASTER_PASSWORD='123456', -> MASTER_HOST='192.168.10.7', -> MASTER_LOG_FILE='master-bin.000001', -> MASTER_LOG_POS=106; Query OK, 0 rows affected (0.06 sec)
mysql> START SLAVE; Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G;
mysql> CHANGE MASTER TO -> MASTER_USER='db1', -> MASTER_PASSWORD='123456', -> MASTER_HOST='192.168.10.8', -> MASTER_LOG_FILE='master-bin.000001', -> MASTER_LOG_POS=106; Query OK, 0 rows affected (0.04 sec)
mysql> START SLAVE; Query OK, 0 rows affected (0.01 sec)
mysql> SHOW SLAVE STATUS\G;
此是数据之间的复制已经实现 |