MYSQL master-slave replication builds the entire process-explore key parameters that cannot be synchronized in real time, mysql master-slave

Source: Internet
Author: User

MYSQL master-slave replication builds the entire process-explore key parameters that cannot be synchronized in real time, mysql master-slave

Reprinted please indicate the source: http://blog.csdn.net/guoyjoe/article/details/45873859

1. master database operations
1. Full Database Export
Mysqldump-h192.168.1.6 -- all-databases-uroot-proot -- opt -- add-drop-database -- add-drop-table -- events -- triggers -- routines -- default-character-set = utf8 -- master-data = 2 -- single-transaction -- complete-insert -- quote-names -- log-error =/gyj/mysqldump2015051_log>/gyj/backup_2015051_mysql


2. Obtain the binlog location of mysql during export.
(Head-50 backup_20150520.mysql or tail-50 backup_20150520.mysql)


---- Change master to MASTER_LOG_FILE = 'binlog. 100', MASTER_LOG_POS = 000001;


3. Copy the backup of the master database to the slave Database
[Root @ uubee6 gyj] # scp backup_2015051_mysql root@192.168.1.8:/gyj/
 
 
Ii. Slave database operations
1. Clear the standby database (retain information_schema, cece_schema, and mysql databases), and then import the data of the primary database.
Check whether the slow log is disabled: set global slow_query_log = off;
If the import is not disabled, the following ERROR occurs: ERROR 1580 (HY000) at line 3405: You cannot 'drop' a log table if logging is enabled.


Mysql-h192.168.1.8-uroot-proot </gyj/backup_20150520.mysql


 
Iii. master database operations
1. Create and copy users
Grant replication slave on *. * TO 'repl' @ '%' identified by 'slavepass ';
 

 

Iv. Slave database operations

1. Configure the connection to the master database in the slave database and run the following command:
CHANGE MASTER
MASTER_HOST = '1970. 168.1.6 ',
MASTER_USER = 'repl ',
MASTER_PASSWORD = 'slavepass ',
MASTER_PORT = 3306,
MASTER_LOG_FILE = 'binlog. 000001 ',
MASTER_LOG_POS = 529372,
MASTER_CONNECT_RETRY = 10;


2. Start slave Database
Mysql> start slave;
Query OK, 0 rows affected (0.01 sec)




3. query the slave status:
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.6
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 657284
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 128192
Relay_Master_Log_File: binlog.000001
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: 657284
Relay_Log_Space: 128358
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: 101
Master_UUID: c811e75a-d875-11e4-8bb1-b083fede6c94
Master_Info_File:/u01/my3306/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



V. Test
1. master database operations
Mysql> select * from t1;
+ ------ +
| Id |
+ ------ +
| 1 |
+ ------ +
1 row in set (0.00 sec)


Mysql> insert into t1 values (2 );
Query OK, 1 row affected (0.00 sec)


Mysql> commit;
Query OK, 0 rows affected (0.00 sec)


Mysql> select * from t1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
+ ------ +
2 rows in set (0.00 sec)


2. Slave database operations
Mysql> select * from t1;
+ ------ +
| Id |
+ ------ +
| 1 |
+ ------ +
1 row in set (0.00 sec)
 
 
3. The slave database is not synchronized, But I operate flush logs in the slave database. The command can be synchronized!




4. Check the parameters of the master database:
Mysql> show variables like '% autocommit % ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Autocommit | ON |
+ --------------- + ------- +
1 row in set (0.00 sec)
 
Mysql> show variables like '% innodb_flush_metho % ';
+ --------------------- + ---------- +
| Variable_name | Value |
+ --------------------- + ---------- +
| Innodb_flush_method | O_DIRECT |
+ --------------------- + ---------- +
1 row in set (0.00 sec)


Mysql> show variables like '% sync_binlog % ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Sync_binlog | 100 |
+ --------------- + ------- +
1 row in set (0.00 sec)



5. Find the cause: sync_binlog = 100. Set the parameter sync_binlog to 1 in the master database !!!
Set global sync_binlog = 1; --- it is best to change the my. cnf configuration file permanently.


 
6. Write down several common commands
Reset master;
Show master status \ G;
Show binlog events in 'binlog. 100 ';
Flush logs;


Related Article

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.