Mysql master-slave data synchronization and backup experience

Mysql tutorial master-slave data synchronization and backup experience

Preparations:
1. Two machines (My winxp ip 192.168.0.119 master server win2003 192.168.0.17)
2. Install the mysql database tutorial server on the two machines respectively (Note: The slave server version is better than the master server version)
3. In order to facilitate memory I call the master server 192.168.0.17 bKjia. c0m-a, from the server 192.168.0.119 bKjia. c0m-b Machine

On the homepage, we configure the master server and create a database a111cnnet to create the table test.

Find [mysqld] In Your my. ini and add

Server-id = 111 # unique identifier, as long as it is not the same as the slave server.
Log-bin = mysql-bin # d:/mysql/log/# Must be enabled here, telling the master server to use binary logs
Binlog-do-db = a111cnnet # the database to be synchronized. Multiple rows can be written.
Binlog-ignore-db = mysql # ignored database, multiple lines can be written

Create an account in bKjia. c0m-a so that bKjia. c0m-b can access the following.

Grant file, replication slave, replication client, super on *. * to backup2 @ '192. 168.0.119 'identified by 'backup2 ';

In this way, we create
Username backup2
Password backup2
Only connection to 192.168.0.119 is allowed.

Good to bKjia. c0m-a configuration, next look at bKjia. c0m-b Configuration

1. Find your my. ini and open it under [mysqld]. Add the following code:

Servers-id = 112
Master-host = 192.168.0.17 # master server ip Address
Master-user = backup2 # master server mysql account, created above
Master-password = backup2 # master server mysql password, created above
Master-connect-retry = 1 # how to set the server reconnect time to 60

Replicate-do-db = a111cnnet # back up the database
Replicate-ignore-db = mysql # ignored Database

Now the configuration is complete, restart bKjia. c0m-a and then start bKjia. c0m-b, And now run show slave status in bKjia. c0m-b

Display
Slave_io_state waiting for master to send event
Slave_io_running yes
Slave_ SQL _running yes

The configuration is successful.

Now you can create a table in bKjia. c0m-a or add a record to see if the data is synchronized.

If not, you delete the master.info in bKjia. c0m-a and bKjia. c0m-b, restart and try again.