MySQL Master-Slave synchronization

1. Primary database operation

(1) Configuring the my.cnf file

Vi/etc/my.cnf

in the [Mysqld] added in:

server-id=1// database ID, main library default 1, other from the library increment, cannot repeat, otherwise synchronization error;

Log-bin//binary log file, required

Binlog-do-db=antic// databases that need to be synchronized 1

Binlog-do-db=delieato// databases that need to be synchronized 2

Binlog-ignore-db=mysql// databases that do not need to be synchronized

after the configuration filehas to be restarted (service mysqld restart), if the restart fails, the configuration file is wrong;

(2) set up a database account for synchronization

in the mysql command line

mysql>grantreplication slave on * * to ' antic ' @ ' 121.41.115.166 ' identified by ' 123456 ';

' antic ': synchronization with the account number;

' 121.41.115.166; the database address of the synchronization;

' 123456 ' : password;

(3) Display the main library information

Mysql>showmaster status;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/57/AF/wKioL1SiYzOiqENKAACZxD6kLMg197.jpg "title=" QQ picture 1.jpg "alt=" Wkiol1siyzoiqenkaaczxd6klmg197.jpg "/>

Seeing the above information indicates that the main library was established successfully.

2, from the library configuration

(1), Back up the database (if the main library is also a new library, do not need this action)

backup databases available with third-party tools (such as navicat) or scripts

For example, to export the ABC database as a database file named Db_abc.sql to the current directory, enter the following command:

#mysqldump-uroot-p ABC > Db_abc.sql

You can enter your password

importing . sql Files

To log in to the database to create a new database name and use

And then

# mysql-u Root-p a </var/www/html/a.sql

or directly on the MySQL command line.

Source/var/www/html/a.sql(also use a database first )

(2), configuration

Vi/etc/my.cnf

Server-id = 2 # here the ID is changed to 2 because the main library is 1;

Log-bin Required fields for data synchronization ;

master-host =115.28.245.30 Main Library IP;

master-user = Test the account used for synchronization ;

Master-password = 123456 Sync account password, the settings when the main library

(3), restart the database, display the status from the server

mysql>show slave status\g; Show

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/57/B2/wKiom1SiYp-SMWDcAALq-RKiXKY946.jpg "title=" QQ picture 2.jpg "alt=" Wkiom1siyp-smwdcaalq-rkixky946.jpg "/>

if slave_io_running and the slave_sql_running are for Yes This means that the configuration from the server is successful, and you can also add data to the primary server for testing.

MySQL Master-Slave synchronization