The method of synchronous backup configuration of Mysql master-slave database _mysql

The following step-by-step to introduce you to the very detailed, specific details please see below.

First, prepare

Test with two servers:

Master Server:192.0.0.1/linux/mysql 4.1.12
Slave Server:192.0.0.2/linux/mysql 4.1.18

Master from the principle of the server is that the MySQL version to the same, if not satisfied, at least from the server MySQL version must be higher than the main server MySQL version

Second, configure Master server

1. Login to master server, edit my.cnf

#vim/etc/my.cnf

In the [Mysqld] section, add the following:

Log-bin=mysql-bin
server-id=1
binlog-do-db=extmail
binlog-ignore-db=mysql,test

Explanation: The Log-bin entry is required for the master server to record binary logs;
SERVER-ID=MASTER_ID wherein the master_id must be a positive integer value between 1 and 232–1;
Binlog-do-db=database is the database to log;
BINLOG-IGNORE-DB is not to log the database name, the middle of multiple databases separated by commas (,);

2. To add a privileged account from the master server to access the master server from the slave server, see the following command:

mysql> grant replication Slave on *.*
-> to ' abc ' @ ' 192.0.0.2 ' identified by ' 123 ';

Format:mysql> GRANT REPLICATION SLAVE on *.*
-> to ' account ' @ ' from server IP or host name ' identified by ' password ';

3. Reset MySQL

4. Backing Up master database data

# mysqldump--master-data extmail > Extmail_backup_20071120.sql

To add the--master-data option, back up the master server's data, and then import the slave server.

5. View Master Status

Mysql> show master status;
+------------------+----------+--------------+------------------+
| File       | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |    79 | Extmail   | mysql,test |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

Third, configure the slave server

1. Editor MY.CNF

# VIM/ETC/MY.CNF

In the [Mysqld] section, add the following:

server-id=2
master-host=192.0.0.1
master-port=3306
Master-user=abc
Master-password=123
Master-connect-retry=60

Explain:

The Server-id of the slave server cannot be the same as master, and the server-id between multiple slave cannot be the same.
Master-host is the host name or IP address of the master server
Master-user and Master-password are ahead of us. Build username and password on master
Master-connect-retry is if the primary server is found broken from the server, the time lag of reconnection

2. Import the database from the primary database server into the server, which is the extmail_backup_20071120.sql in front of us

# mysqladmin Create Extmail
# MySQL Extmail < extmail_backup_20071120.sql

3. Reset MySQL Server

4. Stop slave Service, set various parameters of master server

mysql> slave stop;
mysql> Change Master to
-> master_host= ' 192.0.0.1 ',-> master_user=
' abc ',
-> Master_password = ' 123 ',
-> master_log_file= ' mysql-bin.000002 ',
-> master_log_pos=79;
mysql> slave start;

5. View the status of the master-slave server

Mysql> show Processlist;