MySQL-master-slave replication _ MySQL-mysql tutorial

MySQL-master-slave replication bitsCN.com

MySQL replication is often used in the following environments:

1. avoid disaster through hot backup

2. generate reports

Using the slave server to generate reports can greatly reduce the load on the master server.

3 debugging and auditing

For example, check whether some queries have performance problems and whether the server is not synchronized due to a slot query.

Basic steps for MySQL replication

1. configure a server as the Master.

2. configure a server as Slave.

3. connect the Slave to the Master.

Unless you plan to copy from the very beginning and the configuration in the my. conf file is correct, steps 1 and 2 require that each server be restarted.

Configure Master

Add configuration options to my. conf to configure Master

[mysqld]user          = mysqlpid-file      = /var/run/mysqld/mysqld.pidsocket        = /var/run/mysqld/mysqld.sockport          = 3306basedir       = /usrdatadir       = /var/lib/mysqltmpdir        = /tmplog-bin       = master-binlog-bin-index = master-bin.indexserver-id     = 1

Create a copy user on the Master

master> CREATE USER repl_user;Query OK, 0 rows affected (0.00 sec)master> GRANT REPLICATION SLAVE on *.* to repl_user IDENTIFIED by 'xyzzy';Query OK, 0 rows affected (0.00 sec)

Configure Slave

Add options to the my. conf file to configure Slave

[mysqld]user                = mysqlpid-file            = /var/run/mysqld/mysqld.pidsocket              = /var/run/mysqld/mysqld.sockport                =  3306basedir             =  /usrdatadir             =  /var/lib/mysqltmpdir              =  /tmpserver-id           = 2relay-log-index     = slave-relay-bin.indexrelay-log           = slave-relay-bin

Connect Master and Slave

  slave>  CHANGE MASTER TO      ->     MASTER_HOST  ="master-1",      ->     MASTER_PORT = 3306,      ->     MCSTER_USER = 'repl_user',      ->     MAST_PASSWORD = 'xyzzy';  Query OK, 0 rows affected(0.00 sec)  slave> START SLAVE;

BitsCN.com