配置主从数据库的想法，是从一次面试开始，被面试官问到，不懂就被毙了。参考下面文章终于配置成功。
Mysql复制环境90%以上都是一个Master带一个或者多个Slave的架构模式，主要用于读压力比较大的应用的数据库端廉价扩展解决方案。因为只
要master和slave的压力不是太大(尤其是slave端压力)的话，异步复制的延时一般都很少很少。尤其是自slave端的复制方式改成两个进程 处理之后，更是减小了slave端的延时。而带来的效益是，对于数据实时性要求不是特别的敏感度的应用，只需要通过廉价的pc server来扩展slave的数量，将读压力分散到多台slave的机器上面，即可解决数据库端的读压力瓶颈。这在很大程度上解决了目前很多中小型网站 的数据库压力瓶颈问题，甚至有些大型网站也在使用类似方案解决数据库瓶颈。
主从库是两台服务器上的两个数据库，主库以最快的速度做增删改操作+最新数据的查询操作;
从库负责查询较旧数据，做一些对实效性要求较小的分析，报表生成的工作。这样做将数据库的压力分担到两台服务器上从而保证整个系统响应的及时性。
主从同步数据库，其实就是集群数据库的概念，在客户端看上去数据库只有一个，但是实质上不止一个，可能是多个数据库在维持，实质上说这个技巧是为了保证web平台的稳定而
使用的，这个技巧并不是用来防止数据出错(数据出错站在mysql的层面来看是根本没法避免的，只能靠备份来预防)是为了提高可靠性。
配置时还请注意：
主服务器、从服务器的IP地址可能变化，不要在my.cnf配置固定的master-ip，用命令指定。
主服务器和从服务器都启动ssh服务，方便从服务器远程登录主服务器。
在从服务器上测试主服务器的状态。
mysql配置改变主要要重启服务sudo restart mysql或service mysql restart，我就因为没有及时重启浪费了时间。
配置发生改变后最好重新开启一个命令行终端输入命令。
从数据库的改变也能同步到主数据库
过程 1.1. Master 设置步骤
配置 my.cnf 文件
确保主服务器主机上my.cnf文件的[mysqld]部分包括一个log-bin选项。该部分还应有一个server-id=Master_id选项
# vim /etc/mysql/my.cnf
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = test
binlog_ignore_db = mysql
bind-address默认是127.0.0.1你必须更改它，否则Slave将无法链接到 Master
#bind-address= 127.0.0.1
bind-address= 0.0.0.0
重启服务器
neo@netkiller:~$ sudo /etc/init.d/mysql reload
* Reloading MySQL database server mysqld [ OK ]
建议使用reload,如果不起作用再用restart
登录slave服务器，测试主库3306工作情况，如果看到下面相关信息表示工作正常。
# telnet 192.168.1.246 3306
Trying 192.168.1.246...
Connected to 192.168.1.246.
Escape character is '^]'.
I
5.1.61-0ubuntu0.11.10.1-log1W
创建账户并授予REPLICATION SLAVE权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> FLUSH PRIVILEGES;
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replication@'192.168.245.131' IDENTIFIED BY 'slavepass'
锁表禁止写入新数据
mysql> FLUSH TABLES WITH READ LOCK;
查看Master 工作状态
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 106 | test | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
如果显示下面内容表示，配置不正确
mysql> SHOW MASTER STATUS;
Empty set (0.02 sec)
取得快照并记录日志名和偏移量后，可以在主服务器上重新启用写活动
mysql> UNLOCK TABLES;
5.1.2. Slave
过程 1.2. Slave 设置步骤
配置my.cnf
从服务器的ID必须与主服务器的ID不相同,如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器的以及其它从服务器的不相同。
# vim /etc/mysql/my.cnf
[mysqld]
server-id = 2
# service mysql restart
mysql start/running, process 22893
指定 master 相关参数
在从服务器上执行下面的语句，用你的系统的实际值替换选项值
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;
CHANGE MASTER TO MASTER_HOST='192.168.245.129', MASTER_USER='replication', MASTER_PASSWORD='slavepass';
mysql> CHANGE MASTER TO MASTER_HOST='192.168.245.129', MASTER_USER='repl', MASTER_PASSWORD='slavepass';
Query OK, 0 rows affected (0.14 sec)
启动从服务器线程
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
SLAVE STATUS
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.245.129
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 98
Relay_Master_Log_File:
Slave_IO_Running: Yes
Slave_SQL_Running: 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: 0
Relay_Log_Space: 98
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: NULL
1 row in set (0.00 sec)
5.1.3. Testing
登录 master
复制进程的信息
SHOW PROCESSLIST语句可以提供在主服务器上和从服务器上发生的关于复制的信息
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 62
User: replication
Host: ken-hx409.local:36465
db: NULL
Command: Binlog Dump
Time: 679
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 2. row ***************************
Id: 64
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
2 rows in set (0.00 sec)
登录从库，查看复制线程
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 273
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
*************************** 2. row ***************************
Id: 276
User: system user
Host:
db: NULL
Command: Connect
Time: 2
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 277
User: system user
Host:
db: NULL
Command: Connect
Time: 2
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.00 sec)
如果没有复制进程，请使用start slave;启动
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 273
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
1 row in set (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
登录 master
mysql> insert into foo(id,data) values(2,'Hello world!!!');
Query OK, 1 row affected (0.00 sec)
登录 slave
mysql> select * from foo;
在master服务器上插入一条记录，你可以立刻在slave服务器上看到变化。
5.1.4. 将现有数据库迁移到主从结构数据库
数据库已经存在的情况下怎么迁移
Master 锁表禁止写入新数据
mysql> FLUSH TABLES WITH READ LOCK;
Slave 停止复制进程
mysql> stop slave;
备份Master数据库
mysqldump yourdb | gzip > yourdb.sql.gz
恢复数据库
如果使用mysqldump备份主服务器的数据，将转储文件装载到从服务器
# zcat yourdb.sql.gz | mysql -u root -p yourdb
启动 Slave 复制进程
mysql> start slave;
解除 Master 表锁定
mysql> UNLOCK TABLES;
MyIASM引擎可以采用下面方法
备份数据库
# tar zcvf mysql-snapshot.tar.gz /var/lib/mysql/neo
复制给从数据库
scp mysql-snapshot.tar.gz neo@192.168.245.131:/tmp
snapshot 恢复
$ tar zxvf mysql-snapshot.tar.gz
$ cd /var/lib/mysql
$ mv /tmp/var/lib/mysql/neo .
$ sudo chown mysql.mysql -R neo
重新启动Mysql
$ sudo /etc/init.d/mysql restart
有兴趣可以看看mysqlhotcopy
5.1.5. 主从复制安全问题
复制帐号权限
grant replication slave on *.* to 'replication'@'192.168.1.%' identified by '000000';
主库数据库操作帐号权限
grant DELETE, INSERT, SELECT, UPDATE ON your_user.* to yourdb@'your_host' identified by 'password' with grant option;
从库数据库操作帐号权限
grant SELECT ON your_user.* to yourdb@'your_host' identified by 'password' with grant option;
从库必须收回写操作
5.1.6. 主从不同步问题
执行下面语句
stop slave;
set global sql_slave_skip_counter =1 ;
start slave;
Seconds_Behind_Master 值从NULL变为大于等于0是表示已经同步
Seconds_Behind_Master: NULL
Seconds_Behind_Master: 8893
5.2. Master Master(主主)
5.2.1. Master A
my.cnf 文件加入下面的内容
cp /etc/mysql/my.cnf /etc/mysql/my.cnf.old
vim /etc/mysql/my.cnf
[mysqld]
server-id = 1
log-bin=/data/mysql/binlog/binlog
binlog-do-db = test
binlog-ignore-db=mysql
log-slave-updates
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2
replicate-do-db = test
replicate-ignore-db = mysql,information_schema
创建复制权限
grant replication slave on *.* to 'replication'@'192.168.1.%' identified by '000000';
flush privileges;
mysql>flush tables with read lock;
mysql> show master status\G
*************************** 1. row ***************************
File: binlog.000007
Position: 107
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
5.2.2. Master B
创建复制权限
grant replication slave on *.* to 'replication'@'192.168.1.%' identified by '000000';
flush privileges;
my.cnf 文件加入下面的内容
[mysqld]
server-id = 2
log-bin = /data/mysql/binlog/binlog
replicate-do-db = test
replicate-ignore-db = mysql,information_schema
binlog-do-db = test
binlog-ignore-db=mysql
log-slave-updates
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2
B 与 A 配置文件不同的两处。
server-id = 2
auto_increment_offset=2
mysql> show master status\G
*************************** 1. row ***************************
File: binlog.000005
Position: 107
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
5.2.3. 将Master A 数据库 同步到 Master B 两端数据库内容保持一致
Master A，首先锁表为只读状态
mysqldump --databases test > /tmp/test.sql
Master B 创建一个与Master A同名的空数据库,然后将备份文件恢复到数据库中
# mysql
mysql> CREATE DATABASE test;
mysql>\q
# scp 192.168.1.1:/tmp/test.sql ./
# mysql -uroot -p test < /tmp/test.sql
5.2.4. Master A - B 同步两端数据库
master-A
mysql>change master to master_host='192.168.1.2', master_user='replication', master_password='000000', master_log_file='binlog.000005', master_log_pos=107;
master-B
mysql>change master to master_host='192.168.1.1', master_user='replication', master_password='000000', master_log_file='binlog.000007', master_log_pos=107;
5.2.5. Master A 数据库解除只读权限
Master A 解锁
mysql> UNLOCK TABLES;
5.2.6. 查看主主的工作状态
分别在Master A与B 上运行
mysql>show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5.3. 与复制有关的问题
5.3.1. expire-logs-days
缺省expire-logs-days为30天。这里设为7天，可根据自己情况调整。
[mysqld]
expire-logs-days = 7
5.3.2. Semisynchronous Replication
mysql> SHOW VARIABLES LIKE "have_dynamic_loading";
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| have_dynamic_loading | YES |
+----------------------+-------+
1 row in set (0.00 sec)
mysql>
master > INSTALL PLUGIN rpl_semi_sync_master SONAME ‘libsemisync_master.so’;
slave-x > INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘libsemisync_slave.so’;
master > SET GLOBAL rpl_semi_sync_master_enabled=1;
slave-x > SET GLOBAL rpl_semi_sync_slave_enabled=1;
CREATE USER 'replication'@'192.168.1.%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'replication'@'192.168.1.%' IDENTIFIED BY 'password' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
master_heartbeat_period
STOP SLAVE;
CHANGE MASTER TO master_heartbeat_period= milliseconds;
START SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx', MASTER_USER='root', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_CONNECT_RETRY=10, MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=107;
跳过出问题的MySQL实例
CHANGE MASTER TO MASTER_HOST=xxx.xxx.xxx.xxx IGNORE_SERVER_IDS=y
编辑my.cnf加入
# On Master
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1 second
# On Slave
[mysqld]
rpl_semi_sync_slave_enabled=1
1908
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
