Tumysql5.6 installation and dual-machine Hot Standby Problems and Solutions 2 host configuration salve: Mysql execution on Host: grantreplicationslaveon *. * toslave@172.36.8. % identifiedbypasswd; Modify Master/Slave configuration: 1) host operation # mysql-uroot-passwdmysqlcreatedatabaseyccs _
Ubuntu mysql 5.6 installation and dual-host Hot Standby Problems and Solutions Summary 2 host with salve: Mysql execution on the host: grant replication slave on *. * to slave @ '2017. 36.8.% 'identified by 'passwd'; Modify Master/Slave configuration: 1) host operation # mysql-uroot-passwd mysqlcreate database yccs _
Ubuntu mysql 5.6 installation and dual-host Hot Standby Problems and Solutions 2
Host with salve:
Mysql execution on the host:
Grant replication slave on *. * to slave @ '192. 172.% 'identified by 'passwd ';
Modify the Master/Slave Configuration:
1) host operations
# Mysql-uroot-passwd
Mysql> create database yccs_admin;
Mysql> grant replication slave on *. * to 'root' @ '192. 168.0.124 'identified by 'passwd ';
Edit my. cny
Remove the following comment in mysqld:
Servier-id = 1
Log_bin =/var/log/mysql/mysql-bin.log
Binlog-do-db = your_dbname // if multiple databases need to be synchronized, repeat this parameter and each database row
Binlog-ignore-db = mysql // non-synchronous Database
Restart mysql
Service mysql restart
Run the following command to View Details:
Show master status; // get file and position through this command, which is useful when configuring slave
2) slave Configuration
Edit [mysqld]
Server-id = 2
Replicate-do-db = your_dbname
Replicate-ignore-db = mysql
Slave-skip-errors = 1062 // Add this
Start mysql service again
Service mysql restart
Log on to and create a database:
Mysql-uroot-ppasswd
Create database yccs_admin;
3) run the command on the Master/Slave server.
Master: mysql> flush master; flush logs;
From: mysql> flush slave; flush logs;
4) execute the following command on the slave server:
Mysql> stop slave;
Mysql> change master to master_host = '192. 168.0.123 ', master_user = 'root', master_password = 'passwd', master_port = 192,
Master_log_file = 'mysql-bin.000001 ', master_log_pos = 107;
Mysql> start slave;
Mysql> show slave status \ G
If slave_IO_running: yes slave_ SQL _running: yes, it means there is no problem. You can create a database on the host and write test data. Then
Check whether data is synchronized from the machine.
During this period, you may encounter some problems:
Run:
Show slave status \ G;
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000001 'at 107, the last event read from'/var/log/mysql/mysql-bin.000001 'at 107, the last byte read from '/var/log/mysql/mysql-bin.000001 'at 126.'
Last_ SQL _Errno: 0
Last_ SQL _Error:
ERROR:
Mysql Master/Slave database non-synchronization Solution
The following error occurs: "Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', or the Master/Slave database is not synchronized due to data clearing. The solution is as follows:
First, go to slave and execute: "slave stop;" to stop slave database synchronization;
Run "flush logs;" in the master to clear logs;
Then execute "show master status;" in the master to check the status of the master database, mainly the log file and position;
Mysql> show master status;
+ ------------------ + ---------- + -------------- + -------------------- + ------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ------------------ + ---------- + -------------- + -------------------- + ------------------- +
| Mysql-bin.000006 | 120 | DATABASSE | mysql |
+ ------------------ + ---------- + -------------- + -------------------- + ------------------- +
1 row in set (0.00 sec)
Return TO the slave and run: "change master to MASTER_LOG_FILE = 'Log-bin.000004 ', MASTER_LOG_POS = 106;". The file and location correspond TO those in the master;
Finally, Run "slave start;" in slave to start synchronization.
When you are happy, I am not here, when I am happy, you are not
Change master to master_host = '192. 16.8.66 ', master_user = 'root', master_password = 'passwd', master_port = 172, master_log_file = 'mysql-bin.000006', master_log_pos = 3306;
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16...
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 120
Relay_Log_File: ubuntu-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB: DATABASE
Replicate_Ignore_DB: mysql
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: 120
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: 1
Master_UUID: 8750084d-3197-11e3-863d-0050108b1e91
Master_Info_File:/opt/mysql/data/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
The master-slave synchronization problem has been solved;
