mysql主从热备份
master主服务器：
ip:192.168.5.112
sudo vi /etc/mysql/my.cnf
[mysqld]server-id=1log-bin=mysql-bin#一定要把下面的一句话注销，否则无法远程登录服务器 #bind-address = 127.0.0.1 #这里可以设置哪些库需要备份，哪些不需要，我在这里没有设置 #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name
修改完成之后，运行mysql -uroot -proot
新建查询用户：
mysql> CREATE USER 'repl'@'#' IDENTIFIED BY '12345678';mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'#';
之后，重启服务器，重启之后，再次登录mysql
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 411 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
出现这个，表示master已经设置完成了，接下来设置slave
slave从服务器：
ip：192.168.5.113
sudo vi /etc/mysql/my.cnf
[mysqld] server-id=2
之后登录mysql
mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.5.112', -> MASTER_USER='repl', -> MASTER_PASSWORD='12345678', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=411;
然后重启服务器
之后，登录mysql
mysql > show slave status;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.5.112
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1196
Relay_Log_File: mysqld-relay-bin.000004
Relay_Log_Pos: 1359
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: 1196
Relay_Log_Space: 1696
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: 1
Master_UUID: f4c24d5d-5622-11e7-89df-000c295755b8
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)
ERROR:
No query specified
红色框部分显示yes时，表示主从已经配置成功。之后在主服务器上的mysql新建数据，在从服务器的mysql中查询，看是否成功。
备注：
若主服务器已经存在服务器，则将对应的数据库导出为sql文件
之后，在slave上新建主服务器的数据库，然后将主服务器上的sql文件导入到对应的库中，同步之前数据。
之后，再开始主从复制过程。
