MySQL performance optimization (ix)--Master-slave replication 1

First, the concept

MySQL Replication (replication) is an asynchronous replication that replicates from one MySQL instance (Master) to another MySQL instance (Slave). Implementing the entire master-slave replication requires the IO process on the master server, and the SQL process and IO processes on the slave server to be completed altogether. To achieve master-slave replication, you must first turn on the binary log (Bin-log) function on the master side, because the entire MySQL replication process is actually slave to get the corresponding binary logs from the master side, The various operations that are recorded in the execution log are then fully sequential on their slave side. (The binary log almost records all SQL operation statements against the database except Select)

Second, the problem of replication solution

(1) Data distribution (distribution)

(2) Load balancing (load Balancing)

(3) Backup (Backups)

(4) High-availability and fault-tolerant lines of availability and failover

Three, the principle of replication

(1) Master changes the record to binary log (these are called binary log events, binary logs event);

(2) Slave copies the binary log events of master to its trunk logs (relay log);

(3) Slave redo the events in the relay log and apply the changes to your data.

Describes the process of replication:

The first part of the process is the master record binary log. Master records these changes in two logs before each transaction update data is complete. MySQL writes the transaction serially to the binary log, even if the statements in the transaction are cross-executed. After the event is written to the binary log, master notifies the storage engine to commit the transaction.

The next step is to slave copy the binary log of master to its own trunk logs. First, slave starts a worker thread--i/o thread. The I/O thread opens a normal connection on master and then starts Binlog dump process. Binlog dump process reads the event from the binary log of master, and if it has been followed by master, it sleeps and waits for master to produce a new event. The I/O thread writes these events to the relay log.

The SQL slave thread (SQL slave thread) handles the last step of the process. The SQL thread reads events from the log and replays the events in them to update the slave data so that it is consistent with the data in master. As long as the thread is consistent with the I/O thread, the trunk log is typically located in the OS cache, so the overhead of the trunk log is minimal.

In addition, there is a worker thread in master: As with other MySQL connections, slave opening a connection in master will also cause master to start a thread. The replication process has a very important limitation-replication is serialized on slave, meaning that parallel update operations on Master cannot operate concurrently on slave.

Iv. Configuration

1. Creation of a database: create databases TESTXBQ;

2. Creating table: CREATE table student (ID int PRIMARY KEY auto_increment,name varchar (20));

3. First copy data on Master to slave (based on binary log backup (closed by default))

(1) Open binary log first:

Edit Config file: vi/etc/my.cnf, add in Inside

Log-bin=/var/lib/mysql-bin//binary-stored directory and name

Binlog_format=mixed//

Unique identifier of the server-id=1//Flag Master

(2) Restart Mysql:service mysql restart

(3) Check the status of Master: Show Master State;

(4) To view binaries in the directory: Ll/var/lib/mysql

Among them, the Mysql-bin.index deposit is:

(5) Location-based backup

Mysqldump-uroot-p--single-transaction--master-data=2--triggers--routines--all-databases > Xbq.sql

Based on independent transaction//= 2 comment out =1 not annotated//backup to current directory under Xbq.sql

To view Xbq.sql, you will find:

Subsequent copy work is replicated from the 579-bit position of the mysql-bin.000001.

(6) Master needs to authorize a user to slave, that is, to set up a copy account and execute it on master (can pass?). Grand View Grand's syntax):

Grant Replication Slave on * * to ' reppc ' @ '% ' identified by ' xbq123 '; Generally do not use the root account,% means that all clients may be connected, as long as the account, password is correct, here can be specific client IP instead, such as 192.168.1.112, enhance security.

The refresh permission is required to take effect: flush privileges; (at this point, the database connected to master on salve)

(7) There are two ways to apply Xbq.sql on slave:

Method One: Remote Copy to slaver, SCP [email Protected]:/usr/xbq.sql/usr/xbq.sql

Method Two: Remote connection execution sql, mysql-h192.168.242.129-uroot-p </usr/xbq.sql

(8) Restart Master.

4.slave Application sql

Method One: Source

Method Two: Mysql-uroot-p </usr/xbq.sql

The data on the master in the above operation is slave.

5. The process of copying:

(1) slave start binary log, same as master configuration, but Server-id need =2

　　(2) Let slave connect to master and begin to redo the events in the Master binary log (can pass?). Change master to view syntax):

Change Master to master_host= ' 192.168.242.129 ', master_user= ' REPPC ', master_password= ' xbq123 ', master_port=3306, Master_log_file= ' mysql-bin.000001 ', master_log_pos=579;            

Which location of the log from the server is copied.

Where Master_host is master Ip,master_user is the user who gives replication permission in master, Master_log_file is the binary in master, and Master_log_pos is the location to start copying.

(3) to see if Iothread and Sqlthread are successful, only these two states are yes to replicate.

　　　　

　　(4) View the user table on Maste select * from Mysql.user \g, see a more of the user we just specified, see the reppc:y of this record, that the user has the rights to copy

At this point, Master has been configured OK, check the status: Show Master status;

　　　　

(5) Restart slave first:

　　　　

(6) View salve status show slave status:

　　　　

Slave-io-running:yes,slave-sql-running:yes appears, the master-slave configuration succeeds!

Five. Testing

(1) Add a new record in the student table in Matser:

INSERT into student (name) VALUES (' Xu Bangchi ');

(2) View the student table in slave:

　　　　

Ok!

Vi. Common Commands

1. View binary logs: Mysqlbinlog--no-defaults mysql-bin.000001 (the various DML and DDL included in the binary log).

2. View the MySQL configuration file (my.cnf) directory: Whereis my.cnf

3. View the MySQL configuration file (my.cnf) directory: MySQL--verbose--help | Grep-c 1 ' Default opt '

4. Clear Master:reset Master;

Vii. description

This article is a master one from, of course, a master more from the same reason, in addition to a slave, and then change in my.cnf, and then change the master to, on the master to modify changes master to master_host= ' 192.168.242.% '.

　　

MySQL performance optimization (ix)--Master-slave replication 1