Reprint Please specify source: http://blog.csdn.net/guoyjoe/article/details/45873859
first, the main library operation
1. Full Library Export
mysqldump-h192.168.1.6--all-databases-uroot-proot--opt--add-drop-database--add-drop-table--events--triggers--r Outines--default-character-set=utf8--master-data=2--single-transaction--complete-insert--quote-names-- Log-error=/gyj/mysqldump20150520.log >/gyj/backup_20150520.mysql
2. Get the binlog location of MySQL when exporting
(head-50 backup_20150520.mysql or tail-50 backup_20150520.mysql)
----Change MASTER to master_log_file= ' binlog.000001 ', master_log_pos=529372;
3, copy the main library backup to the standby
[Email protected] gyj]# SCP backup_20150520.mysql [email protected]:/gyj/
Second, prepare the library operation
1. Empty the repository database (keep Information_schema, Performance_schema, MySQL three libraries), then import the data of the main library
Confirm that the slow log has no shutdown: SET GLOBAL slow_query_log=off;
If you do not close the import error: 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
Third, the main library operation
1, build replication users
GRANT REPLICATION SLAVE on * * to ' repl ' @ '% ' identified by ' slavepass ';
Iv. preparation of the library operation
1, in the configuration from the library to connect the main library, execute the following command
Change MASTER to
Master_host= ' 192.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 from the library
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
3, query 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 have 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. Testing
1. Main Library operation
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. Prepare the library operation
Mysql> select * from T1;
+------+
| ID |
+------+
| 1 |
+------+
1 row in Set (0.00 sec)
3, the standby is not synchronized in the past, but I am in the standby Operation flush Logs, command can be synchronized!
4, check the main library on several parameters:
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 reason: sync_binlog=100, in the main library parameters: Sync_binlog set to 1!!!
Set global sync_binlog=1; ---it is best to permanently set the MY.CNF profile
6, write down a few common commands
Reset Master;
Show Master Status\g;
Show Binlog events in ' binlog.000001 ';
Flush logs;
MySQL master-slave replication to build the whole process of real-time---Explore the key parameters that cannot be synchronized
