Ubuntumysql5.6 installation and dual-machine Hot Standby Problems and Solutions 2

Source: Internet
Author: User
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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.