MySQL replication on Ubuntu12.04

MySQL replication on Ubuntu12.04

I. Introduction

MySQL Replication is based on the binnary logging mechanism to synchronize data between the master and slave. Both mechanism, configuration, and operation and maintenance are much simpler and more stable than MSSQL2000 replication (Versions later than mssql2000 have never been used ).

Mysql master writes data updates and changes as events to binary log. Mysql slave reads binary log events and writes the same updates and changes to its own database.

The Master only writes binary logs on its own and does not need to take care of slave. As long as the Server Load balancer is online, data can be synchronized continuously. Even if the server Load balancer is offline, incomplete replication can continue after the Server Load balancer is online. This is very suitable for data backup, because the backup on slave does not affect the operation of the master.

The Master can have multiple slaves, and the slave can also be used as the master and have its own slaves.

Each master and slave must specify a unique server-id in my. cnf.

On slave, replication can be stopped and restored at any time using simple commands.

Binary log has three formats: STATEMENT, ROW, and MIXED. The STATEMENT format is based on SQL statements. It has high performance but does not support some SQL statements. The ROW format is based on rows, which can overcome the shortcomings of the STATEMENT format but generate large logs, STATEMENT is used by default. If the STATEMENT format is not applicable, it is automatically converted to the ROW format. MIXED is recommended. You can set this parameter in my. cnf of the Master.

2. Setting up Mysql replication is very simple. The scenario is as follows.

OS: Ubuntu12.04 X86_64

Master: Host Name mysql-0, IP 192.168.150.200

Slave: Host Name mysql-1, IP 192.168.150.204

1. On the Master:

Edit the/etc/mysql/my. cnf file to make sure there are the following lines (the last three lines are required by the InnoDB engine. We recommend that you add them)
[Mysqld]

Bind-address = 0.0.0.0
Server-id = 1
Log-bin = mysql-binary-log # You can use this name as needed.
Binlog_format = MIXED
Innodb_fast_shutdown = 0
Innodb_flush_log_at_trx_commit = 1
Sync_binlog = 1

Restart the mysql server of the Master node.

Create a user

Mysql> create user 'repl' @ '192. 168.150.200 'identified by 'pass4repl ';
Mysql> grant replication slave on *. * to 'repl' @ '2017. 168.150.200 ';

To obtain binary log coordinates for slave, first make the database temporarily read-only and execute:
Mysql> flush tables with read lock;

The terminal window does not change, and another terminal window is opened to connect to mysql server. Run the following command:
Mysql> show master status;
+ ------------------------- + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------------- + ---------- + -------------- + ------------------ +
| Mysql-binary-log.000001 | 106 |
+ ------------------------- + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

Write down these two values mysql-binary-log.000001, 106, will be used when setting slave later, note, this is my test result, your results may be different. (Binlog_Do_DB and Binlog_Ignore_DB may determine which databases are copied but not copied. This will be studied later)

If data already exists on the Master, you must first use mysqldump or the original data copy to generate a "snapshot" of the existing data, apply it to the Slave server, and then configure the Slave.

Generally, mysqldump is used for backup and then copied to the slave for restoration. Special cases are a little more complex. For details, refer to the official link:
16.1.1.5 Creating a Data Snapshot Using mysqldump
16.1.1.6 Creating a Data Snapshot Using Raw Data Files

If there is no user data on the Master, or you have applied the data snapshot to the Slave, you can execute the command in the terminal window that just executed "flush tables with read lock ;".
Mysql> unlock tables;

To unlock the Master database, and then continue configuring Slave. (Or exit the terminal window)

2. on Slave:

Edit the/etc/mysql/my. cnf file to ensure the following two lines are available:
[Mysqld]

Report-host = 192.168.150.200
Server-id = 2

Restart the mysql server of slave.

Then run the following command in mysql:
Mysql> CHANGE MASTER
-> MASTER_HOST = '192. 168.150.200 ',
-> MASTER_USER = 'repl ',
-> MASTER_PASSWORD = 'pass4repl ',
-> MASTER_LOG_FILE = 'mysql-binary-log.000001 ',
-> MASTER_LOG_POS = 106;

Success!

3. simple task management (result data comes from official documents)

On the Master

Mysql> show processlist \ G;
* *************************** 4. row ***************************
Id: 10
User: root
Host: Server Load balancer 1: 58371
Db: NULL
Command: Binlog Dump
Time: 777
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL

Mysql> show slave hosts;
+ ----------- + -------- + ------ + ------------------- + ----------- +
| Server_id | Host | Port | Rpl_recovery_rank | Master_id |
+ ----------- + -------- + ------ + ------------------- + ----------- +
| 10 | slave1 | 3306 | 0 | 1 |
+ ----------- + -------- + ------ + ------------------- + ----------- +
1 row in set (0.00 sec)

On slave

Mysql> show slave status \ G
Mysql> stop slave;
Mysql> start slave;

Reference: http://dev.mysql.com/doc/refman/5.5/en/replication.html Mysql official documentation written really good, clear and easy to understand.

Disable Mysql replication:
1. Stop replication on Slave and execute:
Mysql> stop slave;

2. on the Master, edit/etc/mysql/my. cnf and comment out the following lines:
Report-host = 192.168.150.200
Server-id = 2

3. Search for the master.info file on Slave:
~ $ Sudo find/-name master.info | grep master.info
After finding it, delete it, rename it, or move it to another directory.
Restart the mysql server of Slave.
Mysqld-relay-bin. * and other files can be deleted.

3. On the Master, edit/etc/mysql/my. cnf and comment out the following lines:
Server-id = 1
Log-bin = mysql-binary-log
Binlog_format = MIXED
Innodb_flush_log_at_trx_commit = 1
Sync_binlog = 1

Innodb_fast_shutdown = 0 can be retained
Restart the mysql server of Slave.
Mysql-binary-log. * and other files can be deleted.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------