xqlee 1345 0 2018-03-24 09:44:10 编程技术 mysql centos6

一准备

首先需要两个MySQL数据库服务器

假如:

数据库服务器1信息

192.168.8.202 3306 root root

数据库服务器2信息

192.168.8.203 3360 root root

 

二、数据库1配置

2.1修改数据库1配置

编辑数据库1的配置文件/etc/my.cnf

[mysqld]节点下添加:

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

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

server_id=202



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

log_bin=mysql-bin



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

binlog-do-db=test



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

#binlog-ignore-db=mysql

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

log_bin_trust_function_creators=TRUE

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

 

1

 

配置方式为指定数据库test同步

2.2重启MySQL服务

重启数据库服务器1MySQL服务

命令:

service mysqld restart

2.3创建slave用户

创建一个slave用户,用于数据库服务器2上进行同步

 

Root登录MySQL数据库

[root@localhost ~]# mysql -uroot -proot

 

创建slave用户

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

 

切换至mysql数据库,查看刚才创建的用户信息

mysql> use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A



Database changed

mysql> select * from user where User='slave' \G;

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

                  Host: %

                  User: slave

              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>

2

注意查看: Repl_slave_priv: Y

Y标识该账户已经有权限操作同步了。

2.3备份数据库

该操作主要是备份数据库服务器1中需要同步的数据库。(这里的同步数据库为test库)

 

  1. 锁表为只读状态

锁表命令:
 

mysql> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.06 sec)

  1. 备份数据库1数据

退出数据库或者新打开一个ssh连接,执行以下命令备份

[root@localhost ~]# mysqldump -uroot -proot test > /root/test-db.sql

3

 

如图已经将test库备份至/root目录,名称为test-db.sql

  1. 查看数据库服务器1master信息

登录数据库执行命令:

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000003 |      887 | test         |                  |                   |

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

1 row in set (0.00 sec)



mysql>

4

记住这里的file名称和Position

  1. 解锁表
mysql> unlock tables;

 

三、数据库2配置

3.1修改数据库2配置

编辑数据库1的配置文件/etc/my.cnf

[mysqld]节点下添加:

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

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

server_id=203



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

log_bin=mysql-bin



#需要备份的数据库名  多个库以逗号分隔

binlog-do-db=test



#忽略备份数据库名

#binlog-ignore-db=mysql



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

log_bin_trust_function_creators=TRUE

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

3.2重启MySQL服务

重启数据库服务器2MySQL服务

命令:

#service mysqld restart

 

3.3导入数据

将数据库服务器1中导出的库导入进来

 

  1. 远程复制备份文件
[root@bogon ~]# scp root@192.168.8.202:/root/test-db.sql /root/test-db.sql

The authenticity of host '192.168.8.202 (192.168.8.202)' can't be established.

RSA key fingerprint is 70:bc:ba:69:58:77:3c:4b:8e:75:75:91:35:fa:40:f6.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.8.202' (RSA) to the list of known hosts.

root@192.168.8.202's password:

test-db.sql                                                                                                                                                                                                                                 100% 2575     2.5KB/s   00:00   

[root@bogon ~]# ll

total 42432

-rw-------. 1 root root     3330 Jul 19 03:54 anaconda-ks.cfg

-rw-r--r--. 1 root root 41789864 Jul 25 07:10 axshool25.sql

drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Desktop

drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Documents

drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Downloads

drwxr-xr-x. 4 root root     4096 Jul 24 06:08 fastDFS

-rw-r--r--. 1 root root    41364 Jul 19 03:54 install.log

-rw-r--r--. 1 root root     9154 Jul 19 03:52 install.log.syslog

drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Music

drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Pictures

drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Public

drwxrwxr-x. 6 root root     4096 May 17 08:39 redis-3.2.9

-rw-r--r--. 1 root root  1547695 May 17 08:40 redis-3.2.9.tar.gz

drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Templates

-rw-r--r--. 1 root root     2575 Jul 26 03:00 test-db.sql

drwxr-xr-x. 2 root root     4096 Jul 19 07:11 Videos

[root@bogon ~]#



导入数据
[root@bogon ~]# mysql -uroot -proot -B test </root/test-db.sql

 

3.4创建slave用户

Root用户登录数据库

[root@bogon ~]# mysql -uroot -proot

 

创建slave用户

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

 

切换至mysql数据库,查看刚才创建的用户信息

mysql> use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A



Database changed

mysql> select * from user where User='slave' \G;

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

                  Host: %

                  User: slave

              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>

5

 

3.5配置master

  1. 首先停止slave
mysql>stop slave

 

  1. 配置master指向数据库服务器1:
mysql> change master to  

    -> master_host='192.168.8.202', 

    -> master_user='slave', 

    -> master_password='123456', 

    -> master_log_file='mysql-bin.000003', 

    -> master_log_pos=887;

Query OK, 0 rows affected, 2 warnings (0.05 sec)



mysql>

 

命令说明:

master_host              :master的主机地址

master_user              :master的备份用户

master_password    :master备份用户密码

master_log_file        :日志文件名,在第二步骤的2.3备份的最后一步查看的信息里面

master_log_pos       :日志位置,同上也在2.3步骤中

 

  1. 启动slave
mysql> start slave;

 

3.6查看数据库服务2master信息

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      120 | test         |                  |                   |

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

1 row in set (0.00 sec)



mysql>

6

 

四、配置数据库1master

  1. Root用户登录数据库1
#mysql –uroot –proot
  1. 停止slave
mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.02 sec)



mysql>
  1. 配置master指向数据库服务器2
mysql> change master to  

    -> master_host='192.168.8.203', 

    -> master_user='slave', 

    -> master_password='123456', 

    -> master_log_file='mysql-bin.000001', 

    -> master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.08 sec)

启动slave
mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

五、测试

 

5.1测试数据库1master创建表同步至数据库2

测试步骤:

  1. Root数据库用户登录数据库服务器1,切换至test

7

  1. 创建一张表,名称为t_data

8

  1. 登录数据库服务器2,切换至数据库test并查看表请情况

 

可以看到数据库2中已经存在t_data表格说明表创建已经同步成功

9

5.2测试数据库1master数据内容同步

测试步骤:

  1. 登录数据库1并切换至test库,新增一条数据到上面创建的t_data表中

10

  1. 登录数据库2,切换至test库查看t_data表数据是否同步

11

可以看到数据库2中的表t_data数据内容已经同步。测试数据同步成功。

 

5.3测试数据库2master创建表同步

测试步骤:

  1. Root数据库用户登录数据库2,并切换至test库,创建一个t_data2的表格

12

  1. 登录数据库1切换至test,查看表t_data2是否创建

13

可以看到在数据库1t_data2表已经同步创建,测试表创建同步成功。

 

5.4测试数据库2master数据内容的同步

测试步骤:

  1. 登录数据库2,并切换至test库向t_data2表中插入一条数据

14

  1. 登录数据库1切换至test库查看表t_data2中数据是否同步

15

可以看到数据已经同步至数据库1中的test库的t_data2表。数据同步测试成功。