MySQL主从结构配置

MySQL主从服务器配置

一、环境说明

1、master服务器

IP：10.26.222.213

Linux：RHEL 6.6

MySQL：5.7.17

port：3306

MySQL已经运行一段时间，有部分数据

 

2、slave服务器

IP：10.26.222.212

linux：RHEL 6.6

MySQL：5.7.17

port：3306

MySQL是新建的，没有数据

 

注：master服务器的MySQL版本不能高于slave服务器的版本

 

二、master服务器配置

1、    关闭MySQL

service mysqld stop

2、    创建存放二进制日志文件夹

mkdir /logs/mysql/bin-log

chown –R mysql:mysql /logs/mysql

3、    修改/etc/my.cnf配置文件

在[mysqld]下面添加：

         server-id=1       #服务器标志号，注意在配置文件中不能出现多个这样的标识，如果出现多个的话mysql以第一个为准，一组主从中此标识号不能重复。

log_bin=/logs/mysql/bin-log/bin-log        #开启bin-log，并指定文件目录和文件名前缀。

Log_bin_index=/logs/bin-log/bin-log.index#记录二进制日志的索引

binlog_format=mixed      #设置bin-log日志文件格式为：MIXED，可以防止主键重复。

max_binlog_size= 200M         #每个bin-log最大大小，当此大小等于200M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中，所以有时日志文件会超过此大小。

binlog_cache_size= 128K        #日志缓存大小

binlog-do-db = autowork #需要同步的数据库名字，多个写多行

binlog-ignore-db= mysql #不需要同步的数据库名字，多个写多行

binlog-ignore-db= sys

binlog-ignore-db= performance_schema

log-slave-updates    #当Slave从Master数据库读取日志时更新新写入日志中，如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新。

expire_logs_days=7         #设置bin-log日志文件保存的天数，此参数mysql5.0以下版本不支持。

注意：如果不定义log_bin的文件名，则会默认以hostname主机名命名，一旦主机名修改，则会因为找不见二进制文件报错。所以最好还是显示指定文件名

4、    打开MySQL

service mysqld start

进入MySQL查看是否配置成功：

mysql -u root -p

mysql> show global variables like'%log%';

5、    新建MySQL用户

新建一个具有复制权限的用户

mysql> grantreplication slave on *.* to ‘slave’@’10.26.222.212’ identified by ‘slavemysql’;

mysql> flush privileges;

 

三、slave服务器配置

1、  关闭MySQL

service mysqld stop

2、  复制数据库

因为master服务器已经运行一段时间，存在一些数据，所以需要将这部分数据复制到slave服务器的数据库。

（1）      冷拷贝

停掉master服务器数据库，然后将master服务器数据库文件拷贝到slave服务器中

scp -r root@10.26.222.213:/mysqldata/test /mysqldata/test

然后更改属主

chown -R mysql:mysql /mysqldata/test

（2）      MySQLdump

首先需要锁住master服务器的数据库，防止操作过程中数据被修改

Mysql>Flush tables with read lock;

然后master服务器另外开一个窗口使用MySQLdump命令备份数据库文件

Shell>mysqldump –u root –p test>test.sql

数据库辈分结束后解锁master服务器

Mysql> unlock tables;

在slave服务器数据库建好test数据库，将备份生成的test.sql文件拷贝到slave服务器，然后恢复（注意需要更改属主属组为mysql:mysql）

Shell>mysql –u root –p test<test.sql

3、  创建存放中继日志的文件夹

mkdir /logs/mysql/relay-log

chown –R mysql:mysql /logs/mysql

4、  修改/etc/my.cnf配置文件

在[mysqld]下面添加：

server-id=2

relay_log=/logs/mysql/relay-log/relay-log

relay_log_index=/logs/mysql/relay-log/relay-log.index

5、  打开MySQL

service mysqld start

进入MySQL查看是否配置成功：

mysql -u root -p

mysql> show global variables like'%log%';

6、  设置从库只读

set globalread_only=1;

只限定非具有SUPER权限的用户只读，具有super权限的用户如root依旧有写权限

Flush tableswith read lock;

限制所有表只读，包括super用户也不能写

 

四、建立连接

1、  在master服务器查看当前使用的二进制日志和位置

mysql> show master status;

记住file（目前操作的二进制日志文件）和position（坐标）。

2、  复制master数据库到slave

因为master服务器已经运行一段时间，存在一些数据，所以需要将这部分数据复制到slave服务器的数据库。可以采用两种方式：

（1）冷拷贝

停掉master服务器数据库，然后将master服务器数据库文件拷贝到slave服务器中

scp -r root@10.26.222.213:/mysqldata/test /mysqldata/test

然后更改属主

chown -R mysql:mysql /mysqldata/test

（2）MySQLdump

首先需要锁住master服务器的数据库，防止操作过程中数据被修改

Mysql>Flush tables with read lock;

然后master服务器另外开一个窗口使用MySQLdump命令备份数据库文件

Shell>mysqldump –u root –p test>test.sql

将备份生成的test.sql文件拷贝到slave服务器恢复,需先建好test数据库

Shell>mysql –u root –p test<test.sql

配置好主从复制结构之后需要解锁master服务器

Mysql> unlock tables;

 

3、  在slave服务器建立连接

mysql> stop slave;

mysql> change master to master_host = '10.26.222.213', master_user= 'replication', master_password = 'slavemysql', master_log_file =  'bin-log.000006', master_log_pos = 154;

mysql> start slave;

 

说明：

master_host：是master服务器的ip

master_user：是master服务器提供的用于复制的MySQL用户名

master_password：是用于复制的MySQL用户的密码

master_log_file：从哪个二进制日志开始复制

master_log_pos：从二进制日志的哪个位置开始复制

 

4、  在slave服务器查看连接是否建立成功

mysql> show slave status\G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

看到这两个字段都为YES即连接建立成功。

5、  在master服务器查看到连接

mysql> show processlist\G;

五、主从复制测试

在master服务器改动数据库数据，在slave服务器可以同步看到相关数据改动，主从服务器配置成功。

 

六、注意事项

1、  主从服务器时间要同步

2、  限制从服务器只读

3、  主从复制安全

设置sync_binlog=1

事务提交立马生效到二进制日志中，防止事务记录还在缓存中，但是服务器挂了，数据就丢失了。