logo
  2. 首页
  3. 教程分享
  4. Java教程
  5. MySQL5.6配置双机互为主备
  6. 详情

MySQL5.6配置双机互为主备

教程分享 > Java教程 (1795) 2024-04-17 12:50:55

一准备

首先需要两个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服务

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

命令:

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. 查看数据库服务器1的master信息

登录数据库执行命令:

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服务

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

命令:

#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查看数据库服务2的master信息

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测试数据库1为master创建表同步至数据库2

测试步骤:

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

7

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

8

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

 

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

9

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

测试步骤:

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

10

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

11

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

 

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

测试步骤:

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

12

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

13

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

 

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

测试步骤:

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

14

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

15

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

https://www.leftso.com/article/243.html
相关文章
centos6 安装mysql 5.6 数据库及配置
centos6 安装mysql 5.6 数据库及配置
centos6.5安装MySQL 5.6版本,并配置数据了链接数量数据库默认字符集utf8mb4支持Emoji表情4字节内容
centos6 mysql5.6 mysql linux
MySQL5.6配置双机互为主备
MySQL5.6配置双机互为主备
MySQL5.6配置双机互为主备
mysql centos6
centos7中mysql配置my.cnf字符集utf-8,mysql5.6中文乱码
centos7中mysql配置my.cnf字符集utf-8,mysql5.6中文乱码
centos7中mysql配置my.cnf字符集utf-8,mysql5.6中文乱码
centos7 mysql mysql中文乱码解决
yum安装jdk1.8-CentOS6.5
yum安装jdk1.8-CentOS6.5
yum安装jdk1.8-CentOS6.5,centos6,jdk8
centos6 jdk8
centos6 redis3.29 安装（yum和源码安装）完整步骤以及常用配置
centos6 redis3.29 安装（yum和源码安装）完整步骤以及常用配置
centos6 yum安装redis3.29,centos6 源码安装redis以及常用配置
centos6 redis centos6安装redis
如何将MySQL数据目录更改为CentOS 7上的新位置
如何将MySQL数据目录更改为CentOS 7上的新位置
如何将MySQL数据目录更改为CentOS 7上的新位置
MySQL Linux centos7
centos6配置开机启动服务chkconfig
centos6配置开机启动服务chkconfig
centos6配置开机启动服务
chkconfig
Centos MySql数据库找回root密码
Centos MySql数据库找回root密码
Centos MySql数据库找回root密码
mysql
centos6.8切换yum源为国内阿里源
centos6.8切换yum源为国内阿里源
centos6.8切换yum源为国内阿里源，默认情况下centos的源是官方的，在国外，下载软件啥的都很慢很慢，这里讲解如何将centos 6.8软件源切换到国内镜像库
centos
centos 6.9minimal版本安装桌面窗口X Window
centos 6.9minimal版本安装桌面窗口X Window
安装系统时候为了快速安装采取centos 6.9minimal版本，偶尔需要使用窗口桌面这里讲解如何安装
centos
MySQL5.6数据库双机主从热备配置
MySQL5.6数据库双机主从热备配置
MySQL5.6数据库双机主从热备配置
MySQL主从 MySQL数据库
centos6.8 yum安装和配置ftp server（vsftpd）客服端
centos6.8 yum安装和配置ftp server（vsftpd）客服端
centos6.8 yum安装和配置ftp server（vsftpd）客服端以及ftp常见问题解决,vsftpd
vsftpd centos ftp
centos5/6/7修改国内阿里云的yum安装源
centos5/6/7修改国内阿里云的yum安装源
centos5/6/7修改国内阿里云的yum安装源
Linux yum yum源
MySQL索引优化_MySQL索引类型_MySQL索引怎么用怎么创建
MySQL索引优化_MySQL索引类型_MySQL索引怎么用怎么创建
MySQL索引优化,MySQL索引类型,MySQL索引怎么用MySQL索引怎么创建这里将会通过一些简单得sql进行讲解
MySQL索引 MySQL 索引 MySQL
MySQL慢查询优化_MySQL慢查询排查_MySQL慢查询设置配置
MySQL慢查询优化_MySQL慢查询排查_MySQL慢查询设置配置
MySQL慢查询优化_MySQL慢查询排查_MySQL慢查询设置配置
mysql mysql调优
热门文章
sql server保留2位/N位小数
[Java教程]
sql server数据库查询将varchar字符型转换为数字float型
[Java教程]
spring boot 入门 spring.profiles.active来分区配置
spring boot 入门 spring.profiles.active来分区配置
[Spring]
Java编程POI处理Excel中添加图片
Java编程POI处理Excel中添加图片
[Java教程]
多肉植物中陶盆与瓷盆
多肉植物中陶盆与瓷盆
[教程分享]