MySQL master/slave server chain copy configuration (ubuntu) _ MySQL

MySQL master/slave server chain copy configuration (ubuntu) Ubuntu

BitsCN.com

MySQL master/slave server chain copy configuration (ubuntu)

Server structure:

Three servers A, B, and C, where A is the news data source, A is the Master of B, B is the Slave of A, and also the Master of C;

Server B copies part of data from server A, and C backs up all data of server;

Configuration:

Master A configuration

Sudo vi/etc/mysql/my. cnf

Delete comments before the following parameters and modify them

Server-id = 1 // allocate server-id

Log-bin = master-bin // Default mysql-bin, which can be left unchanged

Log-bin-index = master-bin.index // not required

Bind-adress = 0.0.0.0 // by default, 127.0.0.1 cannot be accessed because it is not modified.

Modify the system firewall so that server B can access port 3306 (for details, refer to the ufw command)

Restart mysql:

Sudo/init. d/mysql restart

Statement:

Grant replication slave, replication client on *. * TO user @ 'IP B 'IDENTIFIED BY 'password ';

Create an account for server B to connect to server

Go to mysql and run the following command:

Show master status;

View the status of A, record the file location and postion parameters

Slave B configuration

Sudo vi/etc/mysql/my. cnf

Server-id = 2

Log-bin = slave-bin

Bind-address = 0.0.0.0

Relay-log-index = slave-relay-bin.index // not required

Relay-log = slave-relay-bin // not required

Add parameters:

Log-slave-updates = 1

// Normally, the updates received by the slave server from the master server are not recorded in its binary log.

// This option tells the slave server to log the updates executed by its SQL thread to the slave server's own binary log.

Replicate_wild_do_table = copy_db.copy_table // indicates the table in the database to be copied. %

Replicate_wild_ignore_table = ignore_db.ignore_table // non-replicated table

As to why the replicate_do_db and replicate_ignore_db parameters are not used,

An error occurs during cross-database update. if you can ensure that cross-database update is not performed, consider

Restart mysql to enter mysql

Run the following statement:

Change master to MASTER_HOST = 'server A IP ',

MASTER_PORT = 3306,

MASTER_USER = 'user ',

MASTER_PASSWORD = 'password ',

MASTER_LOG_FILE = 'MySQL-bin.000001 ',

MASTER_LOG_POS = 0;

// MASTER_LOG_FILE and MASTER_LOG_POS correspond to the file location and postion parameter in A, indicating the binfile and location to start copying.

Start slave; // start Slave

Show slave status; // view the Slave_IO_State parameter. it is normal if it is Waiting for master to send event.

// Under normal conditions, both Slave_IO_Running and Slave_ SQL _Running are yes

// If the link cannot be properly linked, Slave_IO_State, Slave_IO_Running, Slave_ SQL _Running, and Last_IO_Error

// And other parameters to find the cause of failure

Pass:

Show master status;

Command to record the file location and postion parameters;

Allocate an account to server C for synchronization;

For more information, see A. For more information about Firewall settings, see;

Slave C configuration

Sudo vi/etc/mysql/my. cnf

Server-id = 3

Relay-log-index = slave-relay-bin.index // not required

Relay-log = slave-relay-bin // not required

Use the change master to statement TO modify the master parameters. refer TO the configuration of B;

Pass

Show slave status;

Check the status of C, refer to B

BitsCN.com