Mysql5.5 master-slave replication Configuration

 

1. edit my. cnf in the master database:

[Mysqld]

Log-bin = NEW_NAME

Server-id = 1 # configure a unique ID number, ranging from 1 to 32.

# Set the name of the database for master-Slave replication or not, and set it on Slave.

Binlog-do-db = database name 1

Binlog-do-db = database name 2

Binlog-ignore-db = database name 1

Binlog-ignore-db = database name 2

Mysql> grant replication slave on *. * to slaveuser@192.168.1.101 identified by '2013 ';

Mysql> flush privileges;

Mysql> show master status;

+ ------------------ + ---------- + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000008 | 337 |

+ ------------------ + ---------- + -------------- + ------------------ +

Record the binary log file name and location

 

2. edit my. cnf from the database and change the server-id to a number different from that of the host.

If you only want to synchronize the specified database, you can add replicate-do-db = yourdatabase,

If you want to synchronize a few, just copy a few and change the database name.

[Mysqld]

Server-id = 2 # unique

# Set the name of the database for Master-slave replication or not, and set it on the Master.

Replicate-do-db = database name 1

Replicate-do-db = database name 2

Replicate-ignore-db = database name 1

Replicate-ignore-db = database name 2

 

Mysql> change master to master_host = '192. 168.1.100 ', master_user = 'slaveuser', master_password = '000000', master_log_file = 'mysql-bin.000008', master_log_pos = 192;

Mysql> start slave;

Mysql> show slave status \ G;

If:

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

If both of the preceding values are Yes, it means no problem. If you specify an individual database, you can also see

NOTE: If Slave_ SQL _Running: connecting occurs, first enter:

# Mysql-uslaveuser-h 192.168.1.100-p123456

Check whether you can log on to mysql. If the request is rejected, check whether the slave database authorization operation on the host is set correctly.

 

If the initial environment is not a master-slave architecture and data has been available for a period of time, you must first lock the Host: flush tables with read lock;

Package the data directory of the Host: # tar zcf/tmpdatabak. tgz/opt/data, copy the compressed package to the slave machine, decompress the package, and configure the master.

Then unlock: unlock tables;

 

Iloveyin