MySQL Master-slave replication (Windows)

In our actual development, when the system business to a certain extent, the database may reach a certain bottleneck, but the actual development of the most likely to reach the database bottleneck should be the database read performance, the general business is mostly read and write less, we can improve the performance of the database to improve the overall performance, We can build a master-slave copy of the database cluster, the database read and write separation, implementation in the main library to write, read from the library, when the performance of the read to the bottleneck, you can increase the number of libraries to linearly increase the read performance, but if the main performance of the bottleneck, through master-slave replication is unable to improve the performance of the main OK, so let's start building the master-slave copy results.

1. Install MySQL

Installation Requirements:

(1) You can install MySQL on multiple operating systems (multiple computers or multiple virtual machines on a single computer)

(2) You can also install MySQL on one operating system (using different ports)

Because of the computer performance, I use the second way here.

(1) Extract three times MySQL to folder, respectively named Master,slave1,slave2

(2) Modify the name of the My-small.ini configuration file in master to My.ini

port=3306 (modified mysqld port, not client port)

Server-id=1

Log-bin=mysql-bin Uncomment (remove #)

(3) Modify the name of the My-small.ini configuration file in slave1 to My.ini

port=3307 (modified mysqld port, not client port)

server-id=2

Log-bin=mysql-bin Uncomment (remove #)

(4) Modify the name of the My-small.ini configuration file in Slave2 to My.ini

port=3308 (modified mysqld port, not client port)

Server-id=3

Log-bin=mysqlbin Uncomment (remove #)

Note:

Server-id is the unique identity of the MySQL database, not the same

Master-slave replication needs to be used to log-bin, so you need to configure

The purpose of modifying the port is to run multiple MySQL on one operating system

2. Install and start three services respectively

Go to Master's Bin directory to execute mysqld install Master

net start Master

Go to Slave1 's Bin directory to execute mysqld install slave1

net start slave1

Go to Slave2 's Bin directory to execute mysqld install Slave2

net start Slave2

3. Connect to master server for main library settings

(1) Enter Master's Bin directory to execute mysql-p3306-uroot-p

Connecting to the master database server

(2) Assigning permissions to a library to be connected

mysql> grant replication Slave on * * to ' mysql ' @ ' 192.168.91.92 ' identified by ' 123 '; #分配权限

mysql> flush Privileges; #刷新权限 for permission to take effect

(3) View Main Library status

Mysql> Show master status;

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

|      mysql-bin.000001 |              334 |                  | |

+------------------+----------+--------------+------------------+

Note: Be sure to use the file and position when connecting the main library from the library

　　　　　　

4. Connect to the SLAVE1 server to set up from the library

(1) Enter Slave1 Bin directory to execute mysql-p3307-uroot-p

Connecting to the SLAVE1 database server

(2) Let the Library connect to the main library (using the account assigned in the main library)

Mysql> Change Master to

Master_host= ' 192.168.91.92 ',

Master_user= ' MySQL ',

Master_password= ' 123 ',

Master_log_file= ' mysql-bin.000001 ', #和查看主库状态中的文件一致

master_log_pos=334; #和查看主库状态中的位置一致

(3) Start from library

mysql> start slave;

(4) Show from library status

Mysql> show Slave status\g;

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.91.92 #主库的ip地址

Master_user:mysql #登录主库的账号

master_port:3306 #主库的端口号

Connect_retry:60

master_log_file:mysql-bin.000001

read_master_log_pos:334

relay_log_file:6dwtcvdfbiegwhc-relay-bin.000002

relay_log_pos:253

relay_master_log_file:mysql-bin.000001

Slave_io_running:yes #Yes代表的是与主库连接正常

Slave_sql_running:yes #Yes代表的是从库的执行SQL的线程已经准备就绪

replicate_do_db:

replicate_ignore_db:

Replicate_do_table:

Replicate_ignore_table:

Replicate_wild_do_table:

Replicate_wild_ignore_table:

last_errno:0

Last_error:

skip_counter:0

exec_master_log_pos:334

relay_log_space:419

Until_condition:none

Until_log_file:

until_log_pos:0

Master_ssl_allowed:no

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

seconds_behind_master:0

Master_ssl_verify_server_cert:no

last_io_errno:0

Last_io_error:

last_sql_errno:0

Last_sql_error:

Replicate_ignore_server_ids:

Master_server_id:1

5. Connect to the SLAVE2 server to set up from the library

Connect to the SLAVE2 server first, the other settings and slave1 exactly the same, you can refer to Slave1

6. Testing

You can create a library in the main library, create a table, add data, and then check from the library if there is no synchronization, and if you synchronize the instructions, the environment is no problem.

Well, MySQL master-slave replication environment has been set up, to summarize the advantages and disadvantages of the master-slave replication, in fact, the beginning of this article has been said.

Advantages: Ability to linearly improve the performance of the database read

Cons: Use master-slave replication when write performance reaches bottleneck, no performance improvement

　　　　

　　　　

　　　　　　　

　　　　

MySQL Master-slave replication (Windows)