mysql双机热备环境搭建

一、搭建环境

• 操作系统Linux Centos7
• Mysql版本要求5.7.25
  Master:192.168.199.134
  Slava:192.168.199.118

二、实践步骤

1.在Master节点上开通，slave节点的连接账号
账号：slave02 密码：root

create user 'slave02'@'127.0.0.1'  IDENTIFIED BY 'root';

查询是否创建成功

select * from mysql.user;

在Master上，给用户 REPLICATION SLAVE权限
root 为密码

grant replication slave on *.* to 'slave02'@'192.168.199.118' identified by 'root';
//刷新权限表
flush privileges;

查询是否添加成功

SELECT Repl_slave_priv FROM `user` WHERE `User`="slave02" 

如果有数据写入 阻止
FLUSH TABLES WITH READ LOCK;

在从服务器验证是否可以用创建的用户连接到主服务器

mysql -h 192.168.199.134 -u slave02 -P 53306 -p

二、修改配置文件

[mysqld]

server-id = 1　　　　　　　　//唯一id

log-bin=mysql-bin              //其中这两行是本来就有的，可以不用动，添加下面两行即可.指定日志文件

binlog-do-db = test　　　　 //记录日志的数据库

binlog-ignore-db = mysql    //不记录日志的数据库

重启mysql

查看状态
flush tables with read lock;

查看Master状态
show master status;

unlock tables;

三、配置slave

[mysqld]

server-id = 2

log-bin=mysql-bin

replicate-do-db = test

replicate-ignore-db = mysql,information_schema,performance_schema

重启mysql服务

/usr/share/mysql/mysql_system_tables.sql

ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

导致原因是my.cnf没有同步到文件夹中

执行如下命令

CHANGE MASTER TO MASTER_HOST='192.168.199.134',MASTER_PORT=53309,MASTER_USER='slave02',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=154;

mysql> show slave status;
+----------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Slave_IO_State | Master_Host     | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File                | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File           | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |
+----------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
|                | 192.168.199.134 | slave02     |       53309 |            60 | mysql-bin.000001 |                 154 | 46ddd96b8538-relay-bin.000001 |             4 | mysql-bin.000001      | No               | No                |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                 154 |             154 | None            |                |             0 | No                 |                    |                    |                 |                   |                | NULL                  | No                            |             0 |               |              0 |                |                             |                0 |             | /var/lib/mysql/master.info |         0 | NULL                |                         |              86400 |             |                         |                          |                |                    |                    |                   |             0 |                      |              |                    |
+----------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
1 row in set

执行开启备份命令
start slave；