MySQL master-slave replication principle

Environment:

3306 Main Library

3307 from Library

1 Setting the Server-id value and turning on the Binlog function parameter

Edit the configuration file for MySQL/data/3306/my.cnf

[mysqld]

Server-id = 6----Each machine or instance Sever-id used for synchronization cannot be the same

Log_bin =/data/3306/mysql-bin----can be omitted

Restart Service

/data/3306/mysql restart

Check the idea one

[Email protected] data]# egrep "Log_bin|server-id" 330*/my.cnf

3306/my.cnf:log_bin =/data/3306/mysql-bin

3306/my.cnf:server-id = 6

3307/my.cnf:server-id = 7

Check the train of thought two:

Check Ideas 2:

Login:

[Email protected] data]# mysql-s/data/3306/mysql.sock

To view variables:

Mysql> Show variables like ' log_bin% ';

+---------------------------------+----------------------------+

| variable_name | Value |

+---------------------------------+----------------------------+

| Log_bin | On |

| Log_bin_basename | /data/3306/mysql-bin |

| Log_bin_index | /data/3306/mysql-bin.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

+---------------------------------+----------------------------+

5 rows in Set (0.00 sec)

2 Build Account Authorization "Main Library"

Grant Replication Slave on * * to ' rep ' @ ' 172.16.1.% ' identified by ' oldboy123 ';

Flush privileges;

3 Lock table Export Data

Mysql> Flush table with read lock;

Query OK, 0 rows Affected (0.00 sec)

Check location:

Mysql> Show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+------------------+----------+--------------+------------------+-------------------+

|      mysql-bin.000001 |              405 |                  |                   | |

+------------------+----------+--------------+------------------+-------------------+

1 row in Set (0.00 sec)

New open Window Backup:

Mysqldump-uroot-p ' oldboy123 '-s/data/3306/mysql.sock-a-B |gzip >/server/backup/mysql_bak.$ (date +%F). sql.gz

Original window Unlock:

Mysql> Show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+------------------+----------+--------------+------------------+-------------------+

|      mysql-bin.000001 |              405 |                  |                   | |

+------------------+----------+--------------+------------------+-------------------+

1 row in Set (0.00 sec)

mysql> unlock tables;

Query OK, 0 rows affected (0.01 sec)

The following command replaces all steps of 3

Mysqldump-uroot-p ' oldboy123 '--master-data=2-s/data/3306/mysql.sock-a-B

4. Import data into the from library

[Email protected] backup]# mysql-s/data/3307/mysql.sock <mysql_bak.2017-05-04.sql

5, let the library from the main Library lock table time record Binlog location point start down synchronization

Change MASTER to

Master_host= ' 172.16.1.52 ',

master_port=3306,

Master_user= ' rep ',

Master_password= ' oldboy123 ',

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=405;

This article is from "Xiao Xin" blog, please be sure to keep this source http://12218064.blog.51cto.com/12208064/1926006

MySQL master-slave replication principle