Shulou(Shulou.com)06/01 Report--

The following brings you the idea of realizing mysql active and standby replication-- > using mycat to achieve load balancing, hoping to bring some help to you in practical application. Load balancing involves many things, not many theories, and there are many books on the Internet. Today, we will use the accumulated experience in the industry to do an answer.

1. Main ideas

To put it simply, implement mysql active and standby replication-> use mycat to achieve load balancing.

The commonly used read-write separation methods are compared and mycat is recommended. The community is active and the performance is stable.

2. Test environment

MYSQL version: Server version: 5.5.53. You can download the WINDWOS installation package from the official website. Note: after ensuring that the mysql version is 5.5, the master / slave synchronization configuration of the previous version is different.

The idea of implementing linux is similar, just modify my.cnf.

1) A main mysql. 192.168.110.1 3306, user root, password root. Operating system: win7 x64, memory: 4G

Installation path: C:\ Program Files\ MySQL\ MySQL Server 5.5\ bin

2) B prepare mysql. 192.168.110.2 3306, user root, password root. Operating system: win2003 x64, memory: 1g

Installation path: C:\ Program Files\ MySQL\ MySQL Server 5.5\ bin

3) create sync_test database in the mysql of A master and B slave.

3. Realize mysql active and standby replication.

Main idea: a master mysql opens log, B prepares mysql to read operation log, and executes synchronously.

Generally, it is master / slave synchronization, and master / master synchronization is not recommended.

Configure A master mysql.

1) modify my.ini. You need to create the log directory in the relevant location of the log-bin= "C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log", as well as the mysql-bin.log file. download

Txt code

[mysqld]

Server-id=1 # host label, integer

Port=3306

Log-bin= "C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log" # make sure this file is writable

Read-only=0 # host, both read and write

Binlog-do-db=sync_test # needs to back up the database, multiple writes and multiple lines

Binlog-ignore-db=mysql # databases that do not need to be backed up, multiple writes and multiple lines

2) allow MYSQL remote access

Html code

# Log in to mysql console

Enter% home%/bin and execute mysql-uroot-proot

# Authorization Allow root users to remotely access the A primary mysql from the IP range of 192.168.110.*

Mysql > GRANT ALL PRIVILEGES ON *. * TO 'root'@'192.168.110.*' IDENTIFIED BY' root' WITH GRANT OPTION

# effective. This operation is very important!

Mysql > FLUSH PRIVILEGES

3) restart A main mysql database

Html code

Enter% home%/bin and execute mysql-uroot-proot

Mysql > net stop mysql

Mysql > net start mysql

4) View the status of the master mysql log download

Html code

Mysql > show master status\ G

* * 1. Row *

File: mysql-bin.000003

Position: 107

Binlog_Do_DB: sync_test

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

ERROR:

No query specified

3.2Configuring B standby mysql

1) modify my.ini. You need to create the log directory in the relevant location of the log-bin= "C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log", as well as the mysql-bin.log file. download

Xml code

[mysqld]

# add for sycn test

Server-id=2 # Slave ID

Log-bin= "C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log" # make sure this file is writable

# master-host= "192.168.110.1" # Host Ip

# master-user=root # user name for database access

# master-pass=root # Database access password

# master-port=3306 # Host Port

# master-connect-retry=60 # if the master server is found to be down from the CVM, the time difference between reconnecting (seconds)

Replicate-do-db=sync_test # only copies a library

Replicate-ignore-db=mysql # does not copy a library

2) restart B standby mysql database

Html code

Enter% home%/bin and execute mysql-uroot-proot

Mysql > net stop mysql

Mysql > net start mysql

3) configure the data source of B standby database to verify whether the status of the highlight is normal.

Html code

Mysql > change master to master_host='192.168.110.1',master_port='3306',master_user='root',master_password='root'

Mysql > slave start

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send even

Master_Host: 192.168.110.1

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 107

Relay_Log_File: wjt-1c698d8a032-relay-bin.00001

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: sync_test

Replicate_Ignore_DB: mysql

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: 107

Relay_Log_Space: 565

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

1 row in set (0.00 sec)

ERROR:

No query specified

3.3 verify the synchronization configuration results.

1) A Master mysql: use the navicat tool to create the sync_table table in the sync_test library and add some data.

2) B standby mysql: use the navicat tool to view the sync_ test library, and you can see that the sync_table table and data have been synchronized.

4. Realize the separation of reading and writing.

Main idea: use mycat middleware to forward sql instructions to back-end mysql nodes. Mycat is not responsible for database synchronization.

4.1 install mycat

1) what is mycat? It can be considered as a database access middleware, but it is more like f5, ngnix and other products, with access routing, multi-table slicing operation and other functions. Anyway, it's powerful.

Download, this article uses: 1.6-RELEASE

Mycat is written in java, so multiple versions of linux, windows, and so on are available for download.

2) decompress Mycat-server-1.6-RELEASE-20161012170031-win.tar to the D:\ dev-bin\ mycat directory

3) make sure that the java environment is above jdk1.7, otherwise mycat will not support it.

4) installation completed

4.2 configure mycat

1) server.xml. Configure access users and permissions. Modify the highlight information, in which admin and user are the users who access mycat, and TESTDB is the virtual database of mycat, which is accessed by the upper application.

Html code

Admin

TESTDB

Select user ()

4.3 start mycat

1) start mycat

Html code

D:\ dev-bin\ mycat\ bin > startup_nowrap.bat

The background information is as follows:

Html code

D:\ dev-bin\ mycat\ bin > startup_nowrap.bat

D:\ dev-bin\ mycat\ bin > REM check JAVA_HOME & java

D:\ dev-bin\ mycat\ bin > set "JAVA_CMD=C:\ Program Files (x86)\ Java\ jdk1.7.0_13/bin/java"

D:\ dev-bin\ mycat\ bin > if "C:\ Program Files (x86)\ Java\ jdk1.7.0_13" = "" goto noJavaHome "

D:\ dev-bin\ mycat\ bin > if exist "C:\ Program Files (x86)\ Java\ jdk1.7.0_13\ bin\ java.exe" goto mainEntry

D:\ dev-bin\ mycat\ bin > REM set HOME_DIR

D:\ dev-bin\ mycat\ bin > set "CURR_DIR=D:\ dev-bin\ mycat\ bin"

D:\ dev-bin\ mycat\ bin > cd..

D:\ dev-bin\ mycat > set "MYCAT_HOME=D:\ dev-bin\ mycat"

D:\ dev-bin\ mycat > cd D:\ dev-bin\ mycat\ bin

# if startup fails, modify the following parameters in the D:\ dev-bin\ mycat\ bin\ startup_nowrap.bat file. The default memory footprint is 2G.

Html code

Html code

D:\ dev-bin\ mycat\ bin > "C:\ Program Files (x86)\ Java\ jdk1.7.0_13/bin/java"-server-Xms512m-Xmx512m-XX:MaxPermSize=64M-XX:+AggressiveOpts-XX:MaxDirectMemorySize=768m-DMYCAT_HOME=D:\

P "..\ conf;..\ lib\ *" io.mycat.MycatStartup

. .

MyCAT Server startup successfully. If see logs in logs/mycat.log # starts successfully, you will see the following message.

If information such as 192.168.110.2 not connected appears in the log, please allow B standby mysql remote access.

Html code

# Log in to mysql console

Enter% home%/bin and execute mysql-uroot-proot

# Authorization Allow root users to remotely access Bmysql from the IP range of 192.168.110.*

Mysql > GRANT ALL PRIVILEGES ON *. * TO 'root'@'192.168.110.*' IDENTIFIED BY' root' WITH GRANT OPTION

# effective. This operation is very important!

Mysql > FLUSH PRIVILEGES

4.4 Test the separation of read and write.

1. Verify whether it is synchronized

1) use navicat to connect to mycat in the same way as to connect to the physical mysql library. User admin, password admin, port 8066.

2) in the TESTDB virtual library, create a new table test2 and add some data

3) check that the data of node An and node B have been synchronized.

2. Disable the synchronization of B slave mysql and verify the separation of read and write. download

Html code

Mysql > slave stop

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: 192.168.110.1

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 478

Relay_Log_File: wjt-1c698d8a032-relay-bin.00001

Relay_Log_Pos: 624

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB: sync_test

Replicate_Ignore_DB: mysql

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: 478

Relay_Log_Space: 936

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: NULL

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

1 row in set (0.00 sec)

ERROR:

No query specified

3) use navicat to connect to mycat in the same way as connecting to the physical mysql library, user admin, password admin, port 8066.

a. After the connection is successful, you will see the TESTDB database and the test data table.

b. Add some data to the test table and save it.

c. Perform select * from test to view test, and you will see that the data has not been updated.

Reason: mycat routes the query sq to B, so the result set read is inconsistent.

After reading the above ideas about how to achieve mysql active / standby replication-> using mycat to achieve load balancing, if there is anything else you need to know, you can find out what you are interested in in the industry information or find our professional and technical engineers for answers. Technical engineers have more than ten years of experience in the industry.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.