Build a MySQL Master/Slave Architecture

ArticleDirectory

• 1. install two MySQL services in Windows
• 2. Build the MySQL Master/Slave Architecture
• Iii. Other MySQL

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.

   code highlighting produced by actipro codehighlighter (freeware) 
 http://www.CodeHighlighter.com/
 -->  [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 store it in the MySQL installation path 
   default  -  character  -  set  =  GBK # default character set 
 port  =  3306  #port number  

Open the MS-DOS window,Enter DoSEnvironment,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, copy them to the appropriate location, and rename them. Take my example as an example,D: \ Program Files \ mysql slave 5.1

2. Modify the port number, basedir and datadir.

3. Run the following command to create a service: D: \ Program Files \ mysql slave 5.1 \ bin:Mysqld -- install mysql2

IV,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 addresses are mainly used for synchronization
  Log  -  Bin  =  D: \ data \  Log  \ Master \  Log  -  Bin.  Log  

# Synchronize Databases
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 start
 Log  -  Bin  =  D: \ data \  Log  \ Slave \  Log  -  Bin.  Log  

# Slave ID, different from the host ID
Server  -  ID  =  2  
# Host IP address, used by the slave machine to connect to the host
Master -  Host  =  Localhost
# Host port
Master  -  Port  =  3306  
# The account that was just created to copy host data from the slave machine
Master  -  User  =  Slave
# The new password for copying host data from the slave
Master  -  Password  = 123  
# Retry Interval: 10 seconds
Master  -  Connect  -  Retry  =  10  
# Databases to be synchronized
  Replicate  -  Do  -  DB  =  Renli
# Enable slave database logs for chained Replication
 Log  -  Slave  -  Updates
# Whether the slave database is read-only. 0 indicates read/write, and 1 indicates read-only.
  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.

Iii. Other MySQL

 check what storage engine your MySQL currently provides: mysql> show engines; 
 check the default storage engine of your MySQL: mysql> show variables like '% storage_engine %'; 
 you need to check the engine used by a table (the storage engine used after the parameter engine is displayed in the display result): mysql> show create table table name; 
 lock table: flush tables with read lock;