MySQL master-slave backup in CentOS 5.5

I. system environment:

CentOS 1, 5.5

Host (MASTER): 192.168.0.10

SLAVE (SLAVE): 192.168.0.20

Install Mysql in yum on slave

# Yum install-y mysql-server

Do not enable the mysql service. Copy the/etc/my. cnf file on the master to the/etc directory on the slave.

# Rm-rf/var/lib/mysql-r'' Delete the mysql data directory from the host

Ii. Copy mysql Data on the master to slave

Master

# Vim/etc/my. cnf ''find the mysql data file home directory,/var/lib/mysql

# Cd/var/lib

# Du-h * ''view the mysql Data File Size

# Scp/var/lib/mysql root@192.168.0.2:/var/lib/mysql-r'' copy mysql Data on the master to the slave

{

If the data file is large, you can enable the local terminal in the remote host to avoid errors during terminal copy.

# Screen

# Scp/var/lib/mysql root@192.168.0.2:/var/lib/mysql-r

}

Note the permission of the copied mysql directory # chown mysql. mysql/var/lib/mysql-R

3. modify the configuration file my. cnf

Master

1. Configure/etc/my. cnf

# Vi/etc/my. cnf

Modify [mysqld]

Bind-address = 192.168.0.10 ''Local intranet IP address

Server-id = 10'' ID is unique. The last digit of the internal IP address is usually used in O & M.

Log-bin =/var/lib/mysql/log/mysql-bin''

Binlog-do-db = mysql_db ''indicates the name of the database to be backed up. If multiple databases are backed up, set this option again.

Binlog-ignore-db = XXX' database name that does not need to be backed up.

Log-slave-updates ''must be added; otherwise, the updated log will not be added to the binary file.

Slave-skip-errors ''skips the error and continues the copy operation.

2. Create a user

Use the mysql administrator to access the mysql database

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

Test the connection on slave # mysql-uslave-p123456-h 192.168.0.10

Mysql> fluch tables with read lock; ''LOCK the master database table

Mysql> show master status;

Record master database information

On slave

Modify the configuration file my. cnf

# Vi/etc/my. cnf

Modify or add [mysqld]

Bind-address = 192.168.0.20

Server-id = 20

Log-bin =/var/lib/mysql/log/mysql-bin

Master-host = 192.168.0.10
Master-user = slave
Masters-password = 123456
Master-port = 3306
Replicate-do-db = mysql_db ''name of the database to be backed up
Replicate-ignore-db = XXX' ignored Database
Master-connect-retry = 60'' if the master server is disconnected from the server, the time difference (in seconds)
Log-slave-updates ''must be added; otherwise, the updated log will not be added to the binary file.
Slave-skip-errors ''indicates that the error is skipped and the copy operation continues.

 

#/Etc/init. d/mysqld start Database

Mysql-h192.168.0.10-usalve-p123456

Mysql> show grants for slave@192.168.0.20;

Set synchronization on slave

Mysql> slave stop;
Mysql> change master to MASTER_HOST = '192. 168.0.10 ', MASTER_USER = 'slave', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'mysql-bin.000001 ';

Mysql> slave start; ''start the slave Service

Mysql> show slave status \ G; ''view slave status

The values in the Slave_IO_Running and Slave_ SQL _Running columns are "Yes", indicating that the Slave I/O and SQL threads are running properly.
Mysql> unlock tables;

The building is complete.