mysql5.6 主备_MySQL5.6数据库双机主从热备配置

一、准备

首先安装两个MySQL数据库

Ip地址和端口分别是:

192.168.8.202 3306

192.168.8.203 3306

Root用户及密码

root root

保证两个数据库服务器中需要同步的库内容一致

二、配置从数据库Master

2.1停止master数据库服务

命令:

#service mysqld stop

2.2修改/etc/my.cnf配置文件

在[mysqld]节点添加以下内容:

#######主从配置master信息#######

#[必须]服务器唯一ID，默认是1，一般取IP最后一段

server_id=202

#[必须]启用二进制日志

log_bin=mysql-bin

#需要备份的数据库名  多个库写多行

#binlog-do-db=test

#binlog-do-db=test1

#binlog-do-db=test2

#忽略备份数据库名 多个库写多行

binlog-ignore-db=mysql

#若涉及及同步函数或者存储过程需要配置，否则主备会产生异常不能同步

log_bin_trust_function_creators=TRUE

#######主从配置master信息############

2.3启动MySQL数据库服务

命令:

#service mysqld start

2.4添加从机过来同步数据的用户

首先使用root用户登录master数据库,也就是202

命令:

#mysql –uroot –proot

登录数据库后执行命令

mysql> grant replication slave on *.* to 'slaveuser'@'%' identified by '123456';

Query OK, 0 rows affected (0.06 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.06 sec)

提示:一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.8.203，加强安全。

查看刚才创建的用户授权结果:

命令:

切换至mysql数据库

mysql> use mysql

mysql>select * from user where host='%' and user='slaveuser' \G;

*************************** 1. row ***************************

Host: %

User: slaveuser

Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: Y

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string:

password_expired: N

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>

Repl_slave_priv项为Y，表示授权成功

2.5查看master数据库状态

注意:查看之前做好数据库只读操作,防止在配置过程中出现写入数据日志，导致查询的master日志不可控

查看master数据库状态

命令:

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 |     6187 |              | mysql            |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.01 sec)

mysql>

日志文件名:称 mysql-bin.000002

日志文件位置: 6187

三、配置从数据库slave

如果master中需要同步的数据库已经存在且有数据,则需要将master中的数据库导出到slave保持master和slave需要同步的数据库信息一致。

3.1停止slave数据库服务

命令:

#service mysqld stop

3.2修改/etc/my.cnf配置文件

在[mysqld]的节点下添加以下配置:

注意ID不能重复

3.3启动slave的 MySQL数据库服务

命令:

#service mysqld start

3.4root用户登录从库设置信息

登录数据库:

#mysql –uroot -proot

停止slave

mysql> stop slave;

配置master信息:

mysql>change master to

master_host='192.168.8.202',

master_user='slaveuser',

master_password='123456',

master_log_file='mysql-bin.000002',

master_log_pos=6187;

配置说明:

master_host:master的主机地址

master_user:master上创建的同步数据用户,之前创建的slaveuser

master_password:master创建的同步数据用户密码

master_log_file:master最后一步查看的日志文件名

master_log_pos:master最后一步查看的日志当前位置,同步将从这个点开始

启动slave

mysql>start slave

3.5查看从库配置状态

命令:

Mysql> mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.8.202

Master_User: slaveuser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 23305

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000002

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: 23305

Relay_Log_Space: 457

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

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 202

Master_UUID: 4aed5689-6c69-11e7-9b1f-000c290e4f3d

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

注意,上面两个配置

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Yes标识已经成功配置

四、测试

4.1表创建同步测试

测试步骤方法:在master的test数据库中创建一个表,名称为t_book，然后去slave中test数据库查看表是否同步。

首先查看数据库中已经有的表:

Master中:

#mysql –uroot –proot

Mysql>use test

show tables;

+----------------+

| Tables_in_test |

+----------------+

| t_user         |

+----------------+

1 row in set (0.00 sec)

mysql>

同样查看slave库中的test库

在master的test库中创建一个表,名称为t_book

首先进入MySQL数据库

创建表t_book

mysql> create table t_book(id int,book_name varchar(50));

Query OK, 0 rows affected (0.09 sec)

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| t_book         |

| t_user         |

+----------------+

2 rows in set (0.01 sec)

mysql>

查看slave库中的test

Master已经在test库中创建了一个t_book的表格，现在查看slave的test数据库中是否已经同步

登录slave

#root –uroot –proot

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| t_book         |

| t_user         |

+----------------+

2 rows in set (0.00 sec)

mysql>

可以在slave中看到t_book已经同步过来,

查询数据为空

4.2表数据同步测试

测试方式:

向Master中的test数据库中的t_book表插入一条测试数据,然后去slave的test数据库查询t_book表中是否存在

Master操作:

Slave操作:

Slave中的数据已经同步。