Build the Master/Slave architecture of the MySql database

With the increase in access traffic, some time-consuming database read operations are generally separated by write and read operations to relieve the pressure on the database. The database engine generally uses the Master/Slave architecture. Although this architecture cannot fundamentally solve the database's failed design, it can still play a role in database performance optimization, especially for MySql, there is no unlock operation like SQL Server.

In order to build a Master/Slave environment, due to limited resources, two MySql services are installed on the local machine, one for Master and the other for Slave data synchronization.

1. install two MySql services in windows
Download mysql from the mysql official website. I downloaded mysql 5.1.46. Follow the windows Wizard to install the tool step by step.

After the installation is complete, General requires simple configuration, according to your own machine configuration, from the my-huge.ini, my-innodb-heavy-4G.ini, my-large.ini, my-medium.ini, my-small.ini select a suitable configuration file, copy the file, renamed my. ini to modify the basic configuration information.

[Mysqld] basedir = D: \ Program Files \ MySQL Server 5.1 # MySql installation path datadir = D: \ Data \ MySQL \ data # MySql database storage path, I do not like to save it in the MySql installation path default-character-set = gbk # default character set port = 3306 # port number open the MS-DOS window to enter the DOS environment, switch to the "% MySQL_HOME % \ bin" Directory
Run: % MySQL_HOME % \ bin> mysqld -- install mysql to create the mysql Service (go to Control Panel> verify in service)
Start the service and go to mysql and show databases. Check whether the service is running properly.

After installing a service, you can install the second service.

1. Copy the installed Files to a proper location and rename them. For example, D: \ Program Files \ MySQL Slave 5.1

2. Modify the port number, basedir and datadir.

3. Run D: \ Program Files \ MySQL Slave 5.1 \ Bin in the command line mode to create a service: mysqld -- install mysql2

4. Go to the Registry Editor and find HKEY_LOCAL_MACHINE-> SYSTEM-> CurrentControlSet-> services-> mysql2. edit ImagePath to "" D: \ Program Files \ MySQL Slave 5.1 \ bin \ mysqld "mysql2" and save it.

5. In the command prompt, net start mysql2 to start the service.

Note: If the MySql you have installed already has some data, you need to copy the data to the datadir of the second MySql service.

After the creation is successful, start the mysql2 service and enter mysql2 (with the same password) to verify whether the creation is successful.

2. Build the MySql Master/Slave Architecture
Create a backup account in the database of Master (Mysql5.1). The command is as follows:

Grant replication slave on *. * TO 'slave '@ 'localhost' identified by '123 ';

Modify the Master as follows (my. ini ):

[Mysqld] # Master start # log input address: log-bin = D: \ Data \ log \ Master \ log-bin.log # synchronize database binlog-do-db = renli # The host id cannot be the same as the slave id server-id = 1 # Master end

Modify Slave as follows (my. ini ):

Slave Modification
# Slave startlog-bin = D: \ Data \ log \ Slave \ log-bin.log # Slave machine id, different from the host idserver-id = 2 # Host ip, master-host = localhost # host port master-port = 3306 # account master-user = slave created just for copying host data from the slave machine # Just now it is the slave machine master-password = 123 # retry Interval: 10 seconds master-connect-retry = 10 # database to be synchronized replicate-do-db = renli # enable slave Database logs, in this way, log-slave-updates # Whether the Slave database is read-only. 0 indicates read/write, and 1 indicates read-only = 1 # slave end

On the Master, you can view the status of the current master through show Master status \ G;
On Slave, you can view the status of the current slave through show slave status \ G;

Avoid setting the Master/Slave architecture when data is not synchronized. You can enable and disable synchronization by using start slave and stop slave.