MySql配置主从同步备份【绝对详细】

一. 准备工作

两台服务器：主服务器IP（68.68.89.89）、从服务器IP（29.29.28.28）。

分别安装mysql 5.6，主从服务器数据库端口号分别是23306、83306。

分别在主从服务器上创建iscp数据库。

二. 配置master主服务器

2.1、找到my.ini 配置文件，默认安装一般存放于C:\ProgramData\MySQL\MySQL Server 5.6下面。

2.2、配置my.ini 文件，注意在[mysqld]中添加。

# 服务器唯一标识，主库和从库需要不一致
server-id=1
# 打开日志(主机需要打开)
log-bin=mysql-bin
# 指定要同步的数据库
binlog-do-db=iscp

如下图：

2.3、重启MySql服务

# 停止服务
net start mysql;
# 启动服务
net stop mysql;

2.4、主服务器创建一个新的用户并配置权限，提供从服务器连接

# 创建一个用户名为slave，密码为123456的帐号，只开放给从服务器29.29.28.28连接
grant replication slave on *.* to 'slave'@'29.29.28.28' identified by '123456'; 
# 更新数据库权限 
flush privileges;

2.5、查看主服务器状态

show master status;

注意：这里要拿起小本本记录File和Position的值，分别是mysql-bin.000003、120，后面用到。

三. 配置slave从服务器

3.1、找到my.ini 文件后，配置my.ini 文件，注意在[mysqld]中添加。

# 服务器唯一标识，主库和从库需要不一致
server-id=2
# 打开日志
log-bin=mysql-bin

3.2、重启MySql服务

# 停止服务
net start mysql;
# 启动服务
net stop mysql;

3.3、执行同步命令

# 执行同步命令
change master to master_host='68.68.89.89',master_port=23306,master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=120;
# 开启同步功能 
start slave;

master_host：主服务器IP
master_port：主数据库端口号
master_user：主数据库开放给从数据库的用户账号（2.4配置）
master_password：主数据库开放给从数据库的用户密码（2.4配置）
master_log_file：主数据库日志文件名（对应2.5的File值）
master_log_pos：主数据库日志pos（对应2.5的Position值）

3.4、检查从数据库状态

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 |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+--------------------------------------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+
| Waiting for master to send event |  68.68.89.89  | slave       |       23306 |            60 | mysql-bin.000004 |                 120 | ECSfd3fbc3577f3-relay-bin.000004 |           283 | mysql-bin.000004      | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                 120 |             629 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                1 | 5f2b56ae-32d4-11eb-b8f9-fa163e2aa8ac | C:\ProgramData\MySQL\MySQL Server 5.6\Data\master.info |         0 | NULL                | Slave has read all relay log; waiting for the slave I/O thread to update it |              86400 |             |                         |                          |                |                    |                    |                   |             0 |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+--------------------------------------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+

注意：Slave_IO_Running与Slave_SQL_Running进程必须正常运行（即Yes状态），否则说明同步失败。

到这里，主从数据库设置工作已经完成，接下来在主表进行添加修改删除数据测试一下是否成功。

四. 相关知识

# 停止主从同步
mysql> stop slave; 
 
# 开启主从同步 
mysql> start slave;
 
# 连接断开时，重新连接超时时间 
mysql> change master to master_connect_retry=50; 

五. 遇到的坑

MySql配置文件my.ini都默认有一个uuid（server-id=1），务必保持uuid只有一个，若存在多个MySql只获取到最后出现的。