[mysql] mysql配置主从复制

mysql主从复制

 

随着数据量不断的增加，由单台Mysql作为独立的数据库有的时候是不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。

因此，一般来说都是通过主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力这样的方案来进行部署与实施的。

 

使用mysql主从复制的好处有：

1、采用主从服务器这种架构，稳定性得以提升。如果主服务器发生故障，我们可以使用从服务器来提供服务。

2、在主从服务器上分开处理用户的请求，可以提升数据处理效率。

3、将主服务器上的数据复制到从服务器上，保护数据免受意外的损失。

 

一、MySQL的安装与配置（略）

 

二、MySQL主从复制（全部复制）

场景描述：

主数据库服务器：192.168.10.130，MySQL已经安装，并且无应用数据。

从数据库服务器：192.168.10.131，MySQL已经安装，并且无应用数据。

 

2.1 主服务器上进行的操作

$ vi /opt/mysql/etc/my.cnf
[mysqld]
log-bin=mysql-bin   //[必须]启用二进制日志
server-id=130       //[必须]服务器唯一ID，默认是1，一般取IP最后一段
#启动mysql服务
/opt/mysql/init.d/mysql start
#通过命令行登录管理MySQL服务器
/opt/mysql/bin/mysql -uroot -p'new-password'
#授权给从数据库服务器192.168.10.131
mysql> GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.10.131' identified by ‘password’;
#查询主数据库状态
Mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 261 | | |
+------------------+----------+--------------+------------------+

 记录下 FILE 及 Position 的值，在后面进行从服务器操作的时候需要用到， 保证从192.168.10.131从服务器能够通过rep1登录到192.168.10.130上去。

 

2.2 配置从服务器

#启动mysql服务
/opt/mysql/init.d/mysql start
#通过命令行登录管理MySQL服务器
/opt/mysql/bin/mysql -uroot -p'new-password'
#执行同步SQL语句
mysql> change master to
master_host=’192.168.10.130’,
master_user=’rep1’,
master_password=’password’,
master_log_file=’mysql-bin.000005’,
master_log_pos=261;
#正确执行后启动Slave同步进程
mysql> start slave;
#主从同步检查
mysql> show slave status\G
==============================================
**************** 1. row *******************
Slave_IO_State:
Master_Host: 192.168.10.130
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 415
Relay_Log_File: localhost-relay-bin.000008
Relay_Log_Pos: 561
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: YES
Slave_SQL_Running: YES
Replicate_Do_DB:
……………省略若干……………
Master_Server_Id: 1
1 row in set (0.01 sec)
==============================================

 其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。

 

注意：如果主服务器已经存在应用数据，则在进行主从复制时，需要做以下处理：

//(1)主数据库进行锁表操作，不让数据再进行写入动作
mysql> FLUSH TABLES WITH READ LOCK;

//(2)查看主数据库状态
mysql> show master status;

//(3)记录下 FILE 及 Position 的值。
//将主服务器的数据文件（整个/opt/mysql/data目录）复制到从服务器，建议通过tar归档压缩后再传到从服务器解压。

//(4)取消主数据库锁定
mysql> UNLOCK TABLES;

 

2.3验证（略）

 

2.4.其中碰到的问题有

[1] 新建的用户rep1@localhost不能登录到数据库，rep1@192.168.1.161也自然不能登录

    解决的办法：delete mysql.user where user = ''; 以后/etc/init.d/mysqld restart 重启mysql 就可以了，本人已验证。

 

[2] ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

解决的办法：默认情况下，my.cnf文件中可能没有此行，如果需要设置此数值，手动添加即可。 max_connect_errors = 10

配置说明：

当此值设置为10时，意味着如果某一客户端尝试连接此MySQL服务器，但是失败（如密码错误等等）10次，则MySQL会无条件强制阻止此客户端连接。

如果希望重置此计数器的值，则必须重启MySQL服务器或者执行

1

Mysql> FLUSH—HOSTS;命令。

当这一客户端成功连接一次MySQL服务器后，针对此客户端的max_connect_errors会清零。

功能与作用

一般来说建议数据库服务器不监听来自网络的连接，仅仅通过sock连接，这样可以防止绝大多数针对mysql的攻击；如果必须要开启mysql的网络连接，则最好设置此值，以防止穷举密码的攻击手段。