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

位置:首页>文章>详情   分类: 教程分享 > Java教程   阅读(1040)   2023-03-28 11:29:14

一、准备

首先安装两个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>

 

2

 

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>

3

 

日志文件名: mysql-bin.000002

日志文件位置: 6187

 

三、配置从数据库slave

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

3.1停止slave数据库服务

命令:

#service mysqld stop

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

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

4

注意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)5

注意,上面两个配置

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Yes标识已经成功配置

 

四、测试

4.1表创建同步测试

测试步骤方法:mastertest数据库中创建一个表,名称为t_book,然后去slavetest数据库查看表是否同步。

 

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

Master:

#mysql –uroot –proot



Mysql>use test

show tables;

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

| Tables_in_test |

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

| t_user         |

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

1 row in set (0.00 sec)



mysql>

6

同样查看slave库中的test

7

 

mastertest库中创建一个表,名称为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>

 

8

 

查看slave库中的test

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

 

登录slave

#root –uroot –proot

mysql> show tables;

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

| Tables_in_test |

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

| t_book         |

| t_user         |

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

2 rows in set (0.00 sec)



mysql>

9

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

10

查询数据为空

4.2表数据同步测试

测试方式:

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

 

Master操作:

master

 

Slave操作:

slave

 

Slave中的数据已经同步。

 

地址:https://www.leftso.com/article/240.html

相关阅读

MySQL5.6数据库双机主从热备配置
Centos MySql数据库找回root密码
mysql数据库备份与还原命令_MySQL导出导入数据命令
背景最近有些数据需要处理下,用程序来跑也简单,但是想着能否直接通过数据库来处理。就有了本文的MySQL replace 字符串替换函数使用教程了。MySQL replace 使用参考语句:UPD...
MySQL索引优化,MySQL索引类型,MySQL索引怎么用MySQL索引怎么创建这里将会通过一些简单得sql进行讲解
MySQL5.6配置双机互为主备
centos6.5安装MySQL 5.6版本,并配置数据了链接数量数据库默认字符集utf8mb4支持Emoji表情4字节内容
MySQL慢查询优化_MySQL慢查询排查_MySQL慢查询设置配置
centos7中mysql配置my.cnf字符集utf-8,mysql5.6中文乱码
MySQL查询中null转0