Shulou(Shulou.com)06/02 Report--

First, configure the master database

1. Modify my.cnf or my.ini

[mysqld]

Log-bin=mysql-bin

Server-id=1

Modification completed, restart the server

2. Note:

1) if server_id is not set or set to 0, the master database will reject all connections to the slave database

2) if you are using the innodb storage engine, to achieve better persistence and consistency, you need to configure the following options in my.cnf:

Innodb_flush_log_at_trx_commit=1

Sync_binlog=1

3) make sure that the master database skip_networking system variable is not available, otherwise, the slave server will not be able to connect to the master server, resulting in replication failure

3, create a user for replication

Create a new user repl to grant replication permissions, specifically for master-slave replication.

Mysql > CREATE USER 'repl'@'%.example.com' IDENTIFIED BY' password'

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl'@'%.example.com'

4. Obtain the coordinates of the replication master binary log

1), create a new session on the main library and lock the write operation of the table

Mysql > FLUSH TABLES WITH READ LOCK

Warning: the client that issued the lock operation should keep connected, otherwise the lock will be released.

2) create a new session on the primary database and query the name and location of the binary log file:

Mysql > SHOW MASTER STATUS

Write down the name of the file column and the location of the position column. Later, the slave database will be used. If the master database is not running with the binary log file enabled, the query result may be empty, so in the later slave library configuration, the name is''empty string with the location 4.

5, if the data already exists in the main database

So, don't disconnect the lock table session and move on to the next step

1), create a snapshot of the main library

Mysqldump--all-databases-- master-data > dbdump.db

If you want to ignore some libraries:

Mysqldump-all-databases-ignore-database=db1-ignore-database=db2-ignore-database=db3-master-data > dbdump.db

If you only want to take a snapshot of a database db1:

Mysqldump-databases database1-- master-data > dbdump.db

Or take a snapshot of some databases db1,db2,db3:

Mysqldump-databases db1 db2 db3-- master-data > dbdump.db

2), import snapshot data

Mysql

UNLOCK TABLES

2. Edit the slave library my.cnf and set the server-id. The id must be unique and must not conflict with the master library or any slave library.

[mysqld]

Server-id=2

Setup complete, restart the server

3. Note:

1) if server-id is set to 0, the slave library refuses to connect to the master library

2) if the slave library is used only as a slave library, it is not necessary to open the binary log. If the slave library continues to be the secondary master library, it is necessary to open the binary log.

3) Unix socket files cannot be used for replication. You must be able to connect to the primary MySQL server using TCP/IP

4. If there are no snapshots from the library to import:

1) start the slave library and connect

2) run the slave library setting statement

CHANGE MASTER TO

MASTER_HOST='master_host_name'

MASTER_USER='replication_user_name'

MASTER_PASSWORD='replication_password'

MASTER_LOG_FILE='recorded_log_file_name'

MASTER_LOG_POS=recorded_log_position

3) start the slave library

Start slave

5. If you need to import snapshot data from the library:

1) use the following command to start the slave library

Mysqld-- skip-slave-start &

2) Import snapshot:

Mysql < dbdump.db

3) run the slave library setting statement:

CHANGE MASTER TO

MASTER_HOST='master_host_name'

MASTER_USER='replication_user_name'

MASTER_PASSWORD='replication_password'

MASTER_LOG_FILE='recorded_log_file_name'

MASTER_LOG_POS=recorded_log_position

4) start the slave library

Start slave

The secondary server stores information about the configured primary server in its master information repository. The master information repository can be in the form of files or tables, depending on the system variable master_info_repository setting of the slave library

If master_info_repository=FILE, then it exists in the data directory as files master.info and relay-log.info

If master_info_repository=TABLE, then the information is stored in the master_slave_ information table.

The contents of the main information repository override some of the server options specified on the command line or my.cnf.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.