MySQL read-write separation amoeba&mysql-proxy 02/27 Update SLTechnology News&Howtos

MySQL read-write separation amoeba&mysql-proxy

2025-02-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

-introduction of master-slave synchronization

Refencen https://www.cnblogs.com/lin3615/p/5684891.html

1. Read-write separation mode

Here are two ways, one is to use mysql-proxy, the other is to use Amoeba

Amoeba

Advantages: directly achieve read-write separation and load balancing, no need to modify the code, there is a very flexible data solution

Disadvantages: assign your own account, independent of the permissions management of the back-end database, and the permissions are not flexible enough.

Mysql-proxy

Advantages: directly achieve read-write separation and load balancing without modifying the code, master and slave use the same account

Disadvantages: character set problems, lua programming, or just alpha version, time consuming is a bit high

two。 Read-write separation, delay is a big problem.

Execute show slave status on the slave server to view the synchronization

The name of the master server binary log file that is currently being read by the Imax O thread in Master_Log_File:slave.

Read_Master_Log_Pos: the location in the current master server binary log that has been read by the Icano thread in slave

The Relay_Log_File:SQL thread is currently reading and executing the name of the relay log file

Relay_Log_Pos: the location in the current relay log where the SQL thread has read and executed

Relay_Master_Log_File: the name of the master binary log file that contains most recent events executed by the SQL thread

Whether the Slave_IO_Running:I/ O thread is started and successfully connected to the master

Slave_SQL_Running: whether the SQL thread is started

The gap between the Seconds_Behind_Master:slave server SQL thread and the slave server Ithumb O thread, in seconds

Slave synchronization latency occurs:

1.Seconds_Behind_Master is not 0, this value may be very large

2.Relay_Master_Log_File and Master_Log_File show that there is a big difference in the number of bin-log, indicating that bin-log is not synchronized in time on slave, so the recently executed bin-log is very different from the bin-log read by the current Ibino thread.

There are a large number of mysql-relay-log logs in the slave database directory of 3.mysql. After the synchronization is completed, the log will be automatically deleted by the system, and there are a large number of logs, indicating that the master-slave synchronization delay is very serious.

3. Mysql master-slave synchronization delay principle

Principle of mysql master-slave synchronization

For the read and write operations, the master library writes binlog sequentially, reads the "binlog of write operation" from the single thread of the library to the main library, and fetches from the library to the binlog to execute locally as is (random write) to ensure the logical consistency of master-slave data.

The master-slave replication of mysql is a single-threaded operation, and the master library writes binlog,binlog sequentially to all DDL and DML, so it is very efficient. The Slave_IO_Running thread of slave takes logs from the master database, which is relatively efficient. The next step is that the slave_sql_ running thread of slave implements the DDL and DML operations of the master library in slave. The IO operation of DML,DDL is random, not sequential, and the cost is much higher. It is also possible that other queries on slave generate lock. Because slave_sql_running is also single-threaded, a DDL is stuck and needs to be executed for a period of time. Then all subsequent DDL will wait for the DDL to finish execution before continuing to execute, which leads to delay. Because master can be concurrent, but Slave_sql_ running threads can't, the main library needs to execute DDL for a period of time, and there is a delay when slave executes the same DDL.

Causes of master-slave synchronization delay

When the TPS concurrency of the main library is high, the number of DDL generated is more than a sql thread of Slave can bear, then the delay occurs, and of course, lock waiting may occur with the large query statements of slave.

The first reason is that the database has too much reading and writing pressure on the business, the CPU computing load is heavy, the network card load is heavy, and the hard disk random IO is too high.

Secondary reasons: performance impact of reading and writing binlog, network transmission delay

4. Master-slave synchronization delay solution

Architectural aspect

1. The implementation of the persistence layer of the business adopts a sub-library architecture, and mysql services can be expanded in parallel to disperse the pressure.

two。 A single library is separated from reading and writing, with one master and multiple followers, and the main writer is read from each other, dispersing the pressure.

3. Infrastructure of the service adds a cache layer between the business and mysql

4. The mysql of different businesses is placed on different machines.

5. Use a hardware device that is more advanced than the master as a slave

Anyway, if the mysql pressure becomes smaller, the delay will naturally become smaller.

Hardware:

Use a good server

5. Mysql master-slave synchronous acceleration

1. Sync_binlog is set to 0 on the server.

2.-logs-slave-updates updates received from the server from the master server are not recorded in its binary log.

3. Disable binlog on Slave directly.

4. Server, if the storage engine used is innodb,innodb_flush_log_at_trx_commit = 2

Optimization from the point of view of file system attributes

Master end

Modify the etime attribute of files in linux and Unix file systems. Because OS will write back the time of read operations to disk every time you read files, this is not necessary for database files with frequent read operations, and will only increase the burden on the disk system and affect Ilinux O performance. You can organize the operating system to write atime information by setting the mount attribute of the file system. The operation on linux is as follows:

Open / etc/fstab, plus the noatime parameter

/ dev/sdb1 / data reiserfs noatime 1 2

Then re-mount the file system

# mount-oremount / data

The main library is written, which is highly secure for data. Settings such as sync_binlog=1,innodb_flush_log_at_trx_commit = 1 are required.

While slave does not need such a high level of data security, you can set sync_binlog to 0 or turn off binlog,innodb_flushlog to improve the efficiency of sql execution.

1 、 sync_binlog=1

MySQL provides a sync_binlog parameter to control the binlog of the database to be flushed to disk.

The default, sync_binlog=0, means that MySQL does not control the refresh of binlog, and the file system controls the refresh of its cache. At this time, the performance is the best, but the risk is also the greatest. Once the system Crash, all binlog information in the binlog_cache will be lost.

If sync_binlog > 0, it means that every sync_binlog transaction commits, the refresh operation of the file system called by MySQL brushes the cache. The safest is sync_binlog=1, which means that MySQL will brush down binlog every time a transaction is committed, which is the safest setting with the greatest performance loss. In this way, if the host operating system where the database is located is damaged or suddenly powered off, it is possible for the system to lose the data of a transaction.

However, although binlog is a sequential IO, setting sync_binlog=1 and multiple transactions commit at the same time also greatly affect the performance of MySQL and IO.

Although it can be mitigated by patches in group commit, the high frequency of refreshes can also have a great impact on IO. For systems with highly concurrent transactions, the write performance gap between systems with "sync_binlog" set to 0 and 1 can be as high as five times or more.

So the sync_binlog set by many MySQL DBA is not the safest 1, but 2 or 0. At the expense of a certain amount of consistency, higher concurrency and performance can be achieved.

By default, the binlog is not synchronized with the hard drive every time you write. So if the operating system or machine (not just the MySQL server) crashes, it is possible that the last statement in the binlog is lost. To prevent this, you can use the sync_binlog global variable (1 is the safest value, but also the slowest) to synchronize binlog with the hard drive after every N binlog writes. Even if sync_binlog is set to 1, it is possible that there is an inconsistency between the table content and the binlog content in the event of a crash.

2. Innodb_flush_log_at_trx_commit (this is very useful)

Complain that Innodb is 100 times slower than MyISAM? So you probably forgot to adjust this value. The default value of 1 means that every transaction commit or instruction outside a transaction needs to be written to (flush) the hard disk, which is time-consuming. Especially when using battery powered cache (Battery backed up cache). Setting to 2 is OK for many applications, especially those transferred from the MyISAM table, which means writing to the system cache instead of writing to the hard disk.

Logs are still flush to the hard drive every second, so you don't usually lose updates for more than 1-2 seconds. Setting it to 0 is faster, but the security aspect is poor, and even if the MySQL is down, the transaction data may be lost. A value of 2 will only lose data when the entire operating system is down.

3. The ls command can be used to list the atime, ctime, and mtime of a file.

The access time of the atime file is changed when the file is read or executed

The create time of a ctime file changes as the content of the inode changes when writing to the file, changing the owner, permissions, or link settings

The modified time of a mtime file changes with changes in the contents of the file when writing to the file

Ls-lc filename lists the ctime of the file

Ls-lu filename lists the atime of the file

Ls-l filename lists the mtime of the file

Stat filename lists atime,mtime,ctime

Atime may not be modified after accessing the file

Because: when using the ext3 file system, if you use the noatime parameter when mount, then the atime information will not be updated.

All three time stamp are placed in inode. If the mtime,atime is modified, the inode will certainly change, and now that the inode has changed, so will the ctime.

The reason why we use noatime in mount option is that we don't want file system to make too many changes and improve the performance of reading.

4. Carry on the sub-database and sub-table processing, so as to reduce the replication synchronization operation of the amount of data.

First, MySQL master-slave building 1. Master-slave library definition

Main library 192.168.12.56 3306

From Library 1 192.168.12.56 3307

From library 2 192.168.12.55 3306

two。 Modify parameters of main library

= "192.168.12.56

# vi / etc/my.cnf

[mysqld]

Server-id = 1

Log-bin=mysql-bin

-restart the mysql master

# / etc/init.d/mysqld restart

# netstat-nltpd | grep mysql

-check parameters

# ls-lrth / app/mysql/data/ | grep mysql-bin

-rw-rw---- 1 mysql mysql 107 Oct 29 22:35 mysql-bin.000001

-rw-rw---- 1 mysql mysql 19 Oct 29 22:35 mysql-bin.index

# mysql-uroot-p111111-e 'show variables;' | egrep "log_bin | server_id"

Log_bin ON

Server_id 1

3. Modify parameter 3.1 from library 1

= "192.168.12.56

# vi / mysqldata/3307/my3307.cnf

[mysqld]

Server-id = 2

# mysqladmin-uroot-p1234567-S / mysqldata/3307/mysql3307.sock shutdown

# mysqld_safe-defaults-file=/mysqldata/3307/my3307.cnf 2 > & 1 > / dev/null &

# mysql-uroot-p1234567-S / mysqldata/3307/mysql3307.sock-e 'show variables like "server%"'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 2 | |

3.2 modify from Library 2

= "192.168.12.55

# vi / etc/my.cnf

[mysqld]

Server-id = 3

# / etc/init.d/mysqld restart

# netstat-nltpd | grep mysql

# mysql-uroot-p111111-e 'show variables like "server%"'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 3 | |

+-+ +

4. The main library creates a synchronization account rep

# mysql-uroot-p111111

Mysql > grant replication slave on *. * to 'rep'@'192.168.12.%' identified by' 123456'

Mysql > flush privileges

-replication slave is a necessary permission for mysql synchronization. Do not grant all here

-*. * represents all the tables in all libraries, and you can also specify specific libraries and tables to copy. Shaw_gbk_db.test_tb

-'rep'@'192.168.12.%' rep is the synchronization account, and 192.168.12.% is the authorized host network segment

Mysql > select user,host from mysql.user where user='rep'

+-+ +

| | user | host |

+-+ +

| | rep | 192.168.12.% | |

+-+ +

1 row in set (0.04 sec)

Mysql > show grants for rep@'192.168.12.%'\ G

* * 1. Row *

Grants for rep@192.168.12.%: GRANT REPLICATION SLAVE ON *. * TO 'rep'@'192.168.12.%' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

5. Build from Library 5.1 # # from Library 1 build # (method 1) 5.1.1 Export data from the main library

-the main library is locked. After locking, the window cannot be exited and is affected by the following parameters

Mysql > show variables like'% timeout'

| | interactive_timeout | 28800 | |

| | wait_timeout | 28800 | |

Mysql > flush table with read lock

Note: mysql 5.1 and mysql 5.5 lock tables differently:

Version 5.1: flush tables with read lock

Version 5.5: flush table with read lock

-View the current binlog

Mysql > show master status

+-+

+-+

| | mysql-bin.000001 | 334 | |

+-+

-Open a new window to export data

# mkdir / bak

# mysqldump-uroot-p1111111-A-B-- events | gzip > / bak/mysql_bak_$ (date +% F) .sql.gz

# ll-lrht / bak/

Total 144K

-rw-r--r-- 1 root root 141K Jan 10 07:58 mysql_bak_2018-01-10.sql.gz

-after importing the data, check whether the binlog status is the same as before, and unlock it if it is correct.

Mysql > show master status

+-+

+-+

| | mysql-bin.000001 | 334 | |

+-+

Mysql > unlock tables

-create a database and bring it back to build the slave database to see if it can be synchronized automatically.

Mysql > create database shaw_db

Query OK, 1 row affected (0.02 sec)

Mysql > show master status

+-+

+-+

| | mysql-bin.000001 | 423 |

+-+

Mysql > show processlist

+-- +

+-- +

+-+-+

5.1.2 Import data from Library 1

# gzip-d / bak/mysql_bak_2018-01-10.sql.gz

# ls-lrht / bak/

Total 516K

-rw-r--r-- 1 root root 516K Jan 10 07:58 mysql_bak_2018-01-10.sql

# mysql-uroot-p1234567-S / mysqldata/3307/mysql3307.sock show slave status\ G

Empty set (0.00 sec)

5.1.3 set change master from Library 1

# mysql-uroot-p1234567-S / mysqldata/3307/mysql3307.sock start slave

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0 seconds behind the main database

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.12.56

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 423

Relay_Log_File: orcl-relay-bin.000002

Relay_Log_Pos: 342

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

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

Relay_Log_Space: 497

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)

Mysql > show processlist

+-+-- +-+

+-+-- +-+

+-+-+

3 rows in set (0.00 sec)

-check and find previously created data

Mysql > show databases like 'shaw_db'

+-+

| | Database (shaw_db) |

+-+

| | shaw_db |

+-+

1 row in set (0.00 sec)

5.1.5 Test the synchronization again

-main library

Mysql > use shaw_db

Database changed

Mysql > create table t_zhong as select * from mysql.user

Query OK, 3 rows affected (0.02 sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql > show master status

+-+

+-+

| | mysql-bin.000001 | 537 |

+-+

1 row in set (0.00 sec)

-from Library 1

Mysql > select count (*) from shaw_db.t_zhong

+-+

| | count (*) |

+-+

| | 3 |

+-+

1 row in set (0.00 sec)

-- 5.1.6 log file information generated by the master-slave library

-binlog logs are generated when binlog is enabled in the main database

# ls-lrt | grep bin

-rw-rw---- 1 mysql mysql 33 Jan 10 07:13 mysql-bin.index

-rw-rw---- 1 mysql mysql 827 Jan 10 10:57 mysql-bin.000001

# cat mysql-bin.index

/ mysqldata/3309/mysql-bin.000001

# mysqlbinlog mysql-bin.000001 | more

/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=1*/

/ *! 40019 SET @ @ session.max_insert_delayed_threads=0*/

/ *! 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/

DELIMITER / *! * /

# at 4

# 180110 7:13:05 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.32-log created 1801

10 7:13:05 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACKUPUBERBACKUBUBUBUR

BINLOG'

-opening binlog from the library is not enabled, but there is relaylog and master.info record information

# ls-lrt | grep relay

-rw-rw---- 1 mysql mysql 155 Jan 10 08:20 orcl-relay-bin.000001

-rw-rw---- 1 mysql mysql 48 Jan 10 08:20 orcl-relay-bin.index

-rw-rw---- 1 mysql mysql 746 Jan 10 10:57 orcl-relay-bin.000002

-rw-rw---- 1 mysql mysql 49 Jan 10 10:57 relay-log.info

# cat orcl-relay-bin.index # # relaylog index file

. / orcl-relay-bin.000001

. / orcl-relay-bin.000002

# cat relay-log.info relaylog is a SQL thread

. / orcl-relay-bin.000002

746 # # Relay_Log_Pos: 746 indicates the relaylog location where the log is applied from the library sql.

Mysql-bin.000001 # # this represents the binlog location where data is retrieved from the library from the main database

827 # # Exec_Master_Log_Pos: 827 this is the pos location in the binlog log that retrieves data from the library from the main database

# mysqlbinlog orcl-relay-bin.000002 | more

/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=1*/

/ *! 40019 SET @ @ session.max_insert_delayed_threads=0*/

/ *! 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/

DELIMITER / *! * /

# at 4

# 180110 8:20:25 server id 2 end_log_pos 107 Start: binlog v 4, server v 5.5.32 created 180110

8:20:25

# cat master.info # # master.info is an IO thread

18 # #

Mysql-bin.000001 # # location of the main library binlog

827 # # location of main library pos

192.168.12.55 # # address of the main library

Rep # # main database connection account

123456 # # connection password of main Library

3309 # # Port number of the main library

60 # # indicates that after a problem occurs between the master and slave, the slave library will retry the time

1800.000

5.2 # build from Library 2 # # (method 2) recommend 5.2.1 main database to export data

= "192.168.12.56

-add master-data=1 on export

After the backup is exported by the main library with the parameter master-data=1. After importing the slave library, the following parameters are not required for change master to, but for master-data=2, the following parameters are required.

Master_log_file='mysql-bin.000001'

Master_log_pos=334

# # Export data

# mysqldump-uroot-p111111-A-B-F-- master-data=1-- events-- single-transaction | gzip > / bak/mysqld_$ (date +% F). Sql.gz

# # Parameter description:

-B specify multiple libraries and add library building and use statements

-- compact removes comments, which is suitable for debugging output and is not used in production.

-A back up all libraries

-F refresh binlog log

-- master-data adds binlog log file name and corresponding location point

-x,-- lock-all-tables

Locks all tables across all databases.This is archieved by taking a global read lock for the duration of the whole dump. Automatically turns-single-transaction and-lock-tables off

-l,-- lock-tables Lock all tables for read

-d backup list structure only

-t only back up data

-- single-transaction is suitable for InnoDB transactional database backup

When backing up the InnoDB table, the option-single-transaction is usually enabled to ensure the consistency of the backup. In fact, it works by setting the isolation level of this session to: repeatable read to ensure that the dump of this session will not see the data that has been submitted by other sessions.

Myisam backup command

Mysqldump-uroot-p111111-A-B-F-master-data=2-x-events | gzip > / opt/all.sql.gz

Innodb backup command: recommended

Mysqldump-uroot-p111111-A-B-F-master-data=2-events-single-transaction | gzip > opt/all.sql.gz

5.2.2 Import data from Library 2

= "192.168.12.55

# scp / bak/mysqld_2016-10-30.sql.gz root@192.168.12.55:/root

# gunzip mysqld_2016-10-30.sql.gz

# ls-lrht | grep mysqld

-rw-r--r-- 1 root root 520K Feb 27 14:12 mysqld_2016-10-30.sql

# mysql-uroot-p111111 show slave status\ G

Empty set (0.00 sec)

5.2.3 set change master from Library 2

# mysql-uroot-p1111111 start slave

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.12.55

Master_User: rep

Master_Port: 3309

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 107

Relay_Log_File: mysql3308-relay-bin.000002

Relay_Log_Pos: 480

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1007

Last_Error: Error 'Can't create database' shaw_db'; database exists' on query. Default database: 'shaw_db'. Query: 'create database shaw_db'

Skip_Counter: 0

Exec_Master_Log_Pos: 334

Relay_Log_Space: 1642

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

Last_SQL_Error: Error 'Can't create database' shaw_db'; database exists' on query. Default database: 'shaw_db'. Query: 'create database shaw_db'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)

Error reason: create an object, database, table from the library first. . Then create an object with the same name in the main library and the above error will appear.

5.2.5 handling synchronization failures

-two solutions, one of which is as follows:

Stop slave

Set global sql_slave_skip_counter = 1

Start slave

-the other is as follows: mysql needs to be restarted

Skips the specified error based on the error number.

Slave-skip-errors = 1032, 1062, 1007.

1032: record does not exist

1062: duplicate field value, failed to enter the database

1007: database already exists, failed to create database

1050: datasheet already exists

Generally, failures caused by repeated entry into the library can be ignored. You can also use the all value to ignore all error messages as follows, but it is not recommended. Slave-skip-errors = all

= after processing:

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.12.56

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 107

Relay_Log_File: orcl-relay-bin.000007

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

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

Relay_Log_Space: 554

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)

5.2.6 Test synchronization

-main library operation

Mysql > use shaw_db

Database changed

Mysql > create table t_user as select * from mysql.user

-check from Library 1

Mysql > show slave status\ G

Mysql > select count (*) from shaw_db.t_user

+-+

| | count (*) |

+-+

| | 3 |

+-+

1 row in set (0.01 sec)

-check from Library 2

Mysql > show slave status\ G

Mysql > select count (*) from shaw_db.t_user

+-+

| | count (*) |

+-+

| | 3 |

+-+

1 row in set (0.01 sec)

5.3 create users for application connection to the database

Mysql > grant all privileges on *. * to user01@'192.168.12.%' identified by "111111"

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > show grants for user01@'192.168.12.%'\ G

* * 1. Row *

Grants for user01@192.168.12.%: GRANT ALL PRIVILEGES ON *. * TO 'user01'@'192.168.12.%' IDENTIFIED BY PASSWORD' * FD571203974BA9AFE270FE62151AE967ECA5E0AA'

1 row in set (0.00 sec)

# Note:

= "when logging in, if you log in locally, the default authentication method is local, so the default login will report an error, and you need to specify an ip login. As follows:

# mysql-uuser01-p111111

ERROR 1045 (28000): Access denied for user 'user01'@'localhost' (using password: YES)

# mysql-uuser01-p111111-h 192.168.12.56-P3306

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 20

Server version: 5.5.32-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > exit

Bye

= "the way to deal with this is to re-create the user of local

Mysql > grant all privileges on *. * to user01@'localhost' identified by "111111"

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

# mysql-uuser01-p111111

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 23

Server version: 5.5.32-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql >

Mysql > select user,host from mysql.user

+-+ +

| | user | host |

+-+ +

| | root | 127.0.0.1 | |

| | rep | 192.168.12.% | |

| | user01 | 192.168.12.% | |

| | root | localhost |

| | user01 | localhost |

+-+ +

5 rows in set (0.00 sec)

5.4 insert different data into the master database and the slave database respectively

-the purpose is to facilitate the identification of connected libraries when reading and writing are separated

-Operation on the main database 192.168.12.56 3306

# mysql-uroot-p111111

Mysql > use shaw_db

Mysql > create table t_usedb (id int,name varchar (20))

Mysql > insert into t_usedb values (1)

-operate from Library 1 192.168.12.55 3306

# mysql-uroot-p111111

Mysql > use shaw_db

Mysql > select * from t_usedb

+-+ +

| | id | name |

+-+ +

| | 1 | master |

+-+ +

Mysql > insert into t_usedb values (2pure slave 1')

-operate from Library 2 192.168.12.56 3307

# mysql-uroot-p111111-S / mysqldata/3307/mysql3307.sock

Mysql > select * from shaw_db.t_usedb

+-+ +

| | id | name |

+-+ +

| | 1 | master |

+-+ +

Mysql > insert into shaw_db.t_usedb values (3 famous slave 2')

Second, MySQL read-write separation based on Amoeba

Refencen

Http://blog.chinaunix.net/uid-20639775-id-154600.html

Https://www.cnblogs.com/liuyisai/p/6009379.html

Https://www.cnblogs.com/xyp-blog123/p/6684118.html

1. Amoeba introduction

Amoeba means amoeba and amoeba in Chinese.

At present, in order to achieve the separation of master and slave reading and writing in mysql, there are mainly the following solutions:

a. Through the program implementation, a lot of ready-made code on the Internet, more complex, if you add from the server to change the code of multiple servers.

b. To achieve through mysql-proxy, because the master-slave read-write separation of mysql-proxy is achieved through lua scripts, the current development of lua scripts can not keep up with the pace, and there is no perfect off-the-shelf script, which leads to high risk when used in the production environment. According to many people on the Internet, the performance of mysql-proxy is not high.

c. Self-development interface implementation, this kind of program threshold is high, the development cost is high, not ordinary small companies can afford.

d. Using Alibaba's open source project Amoeba to implement, with load balancing, high availability, sql filtering, read-write separation, routing related query to the target database, and the installation and configuration is very simple.

Amoeba (amoeba) project, focusing on the development of distributed database proxy. Between Client and DB Server (s). Transparent to the client. It has load balancing, high availability, sql filtering, read-write separation, routing related query to the target database, and concurrent requests for merging results of multiple databases.

The main solution is:

? Reduce the complex multi-database structure brought by data segmentation

? Provide segmentation rules and reduce the impact of data segmentation rules on applications

? Reduce the number of connections between db and the client

? Separation of reading and writing

two。 Introduction before deploying the environment

Main library 192.168.12.56 3306

From Library 1 192.168.12.56 3307

From library 2 192.168.12.55 3306

Amoeba 192.168.12.55 8066

The Amoeba framework is developed in JDK1.5 and uses the features of JDK1.5, so you also need to install the java environment. It is recommended to use a JDK version above javaSE1.5.

3. Install the Java environment

Official download address:

Http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

Http://download.oracle.com/otn-pub/java/jdk/8u161-b12/2f38c3b165be4555a1fa6e98c45e0808/jdk-8u161-linux-x64.rpm?AuthParam=1519720220_d473abf93bf78651f1ec927514473d86

Binary installation method:

Here is the rpm package installation:

= "192.168.12.55

# rpm-ivh jdk-8u161-linux-x64.rpm

Preparing... # [100%]

1:jdk1.8 # # [100%]

Unpacking JAR files...

Tools.jar...

Plugin.jar...

Javaws.jar...

Deploy.jar...

Rt.jar...

Jsse.jar...

Charsets.jar...

Localedata.jar...

# rpm-qa | grep jdk1.8

Jdk1.8-1.8.0_161-fcs.x86_64

# rpm-ql jdk1.8-1.8.0_161-fcs.x86_64

/ usr/java/jdk1.8.0_161/... .

# # configuring java environment variables

# vim / etc/profile

# set java environment

Export JAVA_HOME=/usr/java/jdk1.8.0_161

Export JRE_HOME=$JAVA_HOME/jre

Export CLASSPATH=.:$JAVA_HOME/lib:$JRE_HOME/lib:$CLASSPATH

Export PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$PATH

# source / etc/profile

# java-version

Java version "1.8.0,161"

Java (TM) SE Runtime Environment (build 1.8.0_161-b12)

Java HotSpot (TM) 64-Bit Server VM (build 25.161-b12, mixed mode)

4. Install the Amoeba environment

Official download address:

Https://sourceforge.net/projects/amoeba/

Https://nchc.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip

Amoeba installation is very simple and can be used by decompressing it directly. Here, extract the Amoeba to the / usr/local/amoeba directory, so the installation is complete.

= "192.168.12.55

# mkdir / usr/local/amoeba/

# unzip-d / usr/local/amoeba/ amoeba-mysql-3.0.5-RC-distribution.zip

# cd / usr/local/amoeba/

# ls

Benchmark bin conf jvm.properties lib

5. Introduction to the configuration Amoeba5.1 profile

Amoeba has a total of 7 configuration files, one of which is as follows:

# / * Amoeba master configuration file ($AMOEBA_HOME/conf/amoeba.xml), which is used to configure the basic parameters of the Amoeba service, such as Amoeba host address, port, authentication method, user name for connection, password, number of threads, timeout, location of other configuration files, etc.

# / * Database server configuration file ($AMOEBA_HOME/conf/dbServers.xml), which is used to store and configure the information of the database server represented by Amoeba, such as host IP, port, user name, password, etc.

# / * sharding rule configuration file ($AMOEBA_HOME/conf/rule.xml), which is used to configure sharding rules.

# / * the database function configuration file ($AMOEBA_HOME/conf/functionMap.xml) is used to configure the handling of the database function, and Amoeba will use the method in this configuration file to parse the database function.

# / * sharding rule function configuration file ($AMOEBA_HOME/conf/ruleFunctionMap.xml), which is used to configure the handling method of user-defined functions used in sharding rules.

# / * access rule configuration file ($AMOEBA_HOME/conf/access_list.conf), which is used to authorize or prohibit some server IP from accessing Amoeba.

# / * Log specification configuration file ($AMOEBA_HOME/conf/log4j.xml), which is used to configure the level and method of Amoeba output logs.

5.2 introduction to configuration script

The configuration file for Amoeba is located in the / usr/local/amoeba/conf directory in this environment. There are many configuration files, but only using the read-write separation feature, you only need to configure two files, dbServers.xml and amoeba.xml. If you need to configure ip access control, you also need to modify the access_list.conf file.

# Note:

The script uses "" to indicate comments, such as the following paragraph, which is actually annotated.

Let's first introduce dbServers.xml

[root@bogon amoeba] # cat conf/dbServers.xml

${defaultManager}

sixty-four

one hundred and twenty eight

3306 # set the port of the mysql database to which Amoeba connects. The default is 3306.

Testdb # sets the default database. When connecting to amoeba, the operation table must explicitly specify the database name, that is, dbname.tablename. Use dbname is not supported to specify the default library, because the operation will be scheduled to each back-end dbserver.

Test1 # sets the account and password for amoeba to connect to the back-end database server, so you need to create the user on all back-end databases and authorize the amoeba server to connect

111111

500 # maximum number of connections. Default is 500

500 # maximum idle connections

1 # latest idle connections

600000

True

# set a backend writable dbServer, which is defined as writedb. This name can be named arbitrarily, and will be used later.

192.168.2.204 # set backend writable dbserver

# set backend readable dbserver

192.168.2.205

# setting defines a virtual dbserver, which is actually equivalent to a dbserver group. Here, the readable database ip is put into a group, and the name of this group is named myslave.

1 # Select the scheduling algorithm, 1 indicates replication equilibrium, 2 indicates weight, 3 indicates HA, here select 1

Slave # myslave group member

Another profile, amoeba.xml

[root@bogon amoeba] # cat conf/amoeba.xml

8066 # set the port for amoeba listening. Default is 8066.

# configure the listening API below. If it is not set, it listens to all IP by default.

one hundred and twenty eight

sixty-four

# you need to use the account set here when the client connects to amoeba (the account password here has nothing to do with the password of amoeba connecting to the backend database server)

Root

123456

${amoeba.home} / conf/access_list.conf

one hundred and twenty eight

five hundred

Utf8

sixty

Com.meidusa.toolkit.net.AuthingableConnectionManager

${amoeba.home} / conf/dbServers.xml

${amoeba.home} / conf/rule.xml

${amoeba.home} / conf/ruleFunctionMap.xml

${amoeba.home} / conf/functionMap.xml

1500

Writedb # sets the default pool for amoeba, here it is set to writedb

Writedb # is logged out by default and needs to be uncommented. Here, it is used to specify the two read and write pools defined earlier.

Myslave #

True

5.3 configuration script dbServers.xml

First notice that this is an abstract parent service that is used for inheritance and use of the actual service. Each server should have its own back-end mysql connection port, database name, account number, password, connection pool size, etc. Written in the abstract parent class, convenient for unified management. If you don't need inheritance, you can copy it to each specific server.

Create two more actual server. All inherit the same parent class, except that their IP

Then configure a virtual server, which is equivalent to freely combining the actual server into a server, named multiPool here, and the name can be customized.

# cd / usr/local/amoeba/conf/

# vi dbServers.xml

${defaultManager}

sixty-four

one hundred and twenty eight

3306

Shaw_db

User01

111111

five hundred

one

600000

True

192.168.12.56

192.168.12.55

one

Slave1

5.4 configuration script amoeba.xml

Configure the connection port of amoeba and the login account password of amoeba. Unlike the account password in dbServers.xml, dbServers.xml is the account password of configuring backend mysql.

Set the default database to master, as well as the read and write policy, where the read policy is polling from the virtual service. When a sql statement cannot be parsed and cannot be properly routed to writePool or readPool, it will be routed to defaultPool. Therefore, defaultPool is generally configured as the primary node.

# cd / usr/local/amoeba/conf

# vi amoeba.xml

8066

192.168.12.55

one hundred and twenty eight

sixty-four

Amobeba

123456

${amoeba.home} / conf/access_list.conf

one hundred and twenty eight

five hundred

Utf8

sixty

Com.meidusa.toolkit.net.AuthingableConnectionManager

${amoeba.home} / conf/dbServers.xml

${amoeba.home} / conf/rule.xml

${amoeba.home} / conf/ruleFunctionMap.xml

${amoeba.home} / conf/functionMap.xml

1500

Master

Myslave

True

Start the Amoeba service 5.5.1 start the service

# / usr/local/amoeba/bin/launcher

Java HotSpot (TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0

Java HotSpot (TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0

The stack size specified is too small, Specify at least 228k

Error: Could not create the Java Virtual Machine.

Error: A fatal exception has occurred. Program will exit.

5.5.2 processing error report

From the error text point of view, the stack size should be too small, resulting in JVM startup failure, how to modify it?

In fact, Amoeba has taken this problem into account and has written the JVM parameter configuration in the properties file. Now, let's modify the JVM parameter through the properties file.

Modify the JVM_OPTIONS parameter of the jvm.properties file.

# vim / usr/local/amoeba/jvm.properties

Change it to:

JVM_OPTIONS= "- server-Xms1024m-Xmx1024m-Xss256k-XX:PermSize=16m-XX:MaxPermSize=96m"

Originally:

JVM_OPTIONS= "- server-Xms256m-Xmx1024m-Xss196k-XX:PermSize=16m-XX:MaxPermSize=96m"

# / usr/local/amoeba/bin/launcher

2018-03-06 14 1712 281 INFO net.ServerableConnectionManager-Server listening on 0.0.0.0 Univer 8066.

2018-03-06 15:19:46 [INFO] Project Name=Amoeba-MySQL, PID=34261, System shutdown....

Java HotSpot (TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0

Java HotSpot (TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0

2018-03-06 16:12:06 [INFO] Project Name=Amoeba-MySQL, PID=34592, starting...

Log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml

2018-03-06 16 1214 06852 INFO context.MysqlRuntimeContext-Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA

Log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf

2018-03-06 16 INFO net.ServerableConnectionManager 12 07142 8066 / 192.168.12.55

2018-03-06 16:22:06 [INFO] Project Name=Amoeba-MySQL, PID=34592, System shutdown....

Java HotSpot (TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0

Java HotSpot (TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0

2018-03-06 16:22:54 [INFO] Project Name=Amoeba-MySQL, PID=34684, starting...

Log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml

2018-03-06 16 22 54571 INFO context.MysqlRuntimeContext-Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA

Log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf

2018-03-06 16 INFO net.ServerableConnectionManager 22 INFO net.ServerableConnectionManager-Server listening on / 192.168.12.55

5.5.3 check the service port

# netstat-tlnp | grep java

Tcp 0 0: 8066: * LISTEN 32534/java

5.5.4 stop service

# / usr/local/amoeba/bin/shutdown

Kill-15 34592

6. Test Amoeba master-slave read and write

# # pay attention to the login prompt amoeba

# mysql-uamobeba-p123456-h 192.168.12.55-P8066

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 2012979869

Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql >

# # We have previously inserted a piece of slave1 data from Library 1. Here we can make sure that the connection is from Library 1.

Mysql > select * from t_usedb

+-+ +

| | id | name |

+-+ +

| | 1 | master |

| | 2 | slave1 |

+-+ +

# # insert a piece of data

Mysql > insert into t_usedb values (55)

Query OK, 1 row affected (0.10 sec)

Mysql > select * from t_usedb

+-+ +

| | id | name |

+-+ +

| | 1 | master |

| | 2 | slave1 |

| | 55 | amoeba |

+-+ +

# at this point, we log in to the main database to check whether there is any newly inserted data.

# mysql-uroot-p111111

Mysql > select * from shaw_db.t_usedb

+-+ +

| | id | name |

+-+ +

| | 1 | master |

| | 55 | amoeba |

+-+ +

# at this point, we log in to Slave 2 to see if there is any newly inserted data.

# mysql-uroot-p111111-S / mysqldata/3307/mysql3307.sock

Mysql > select * from shaw_db.t_usedb

+-+ +

| | id | name |

+-+ +

| | 1 | master |

| | 3 | slave2 |

| | 55 | amoeba |

+-+ +

# We put the main library stop, and then insert the data to see if there is an error

# / etc/init.d/mysqld stop

# mysql-uamobeba-p123456-h 192.168.12.55-P8066

Mysql > insert into t_usedb values (100gramme amoeba222')

ERROR 1044 (42000): Amoeba could not connect to MySQL server [192.168.12.56 Connection refused]

Mysql > select * from t_usedb

+-+ +

| | id | name |

+-+ +

| | 1 | master |

| | 2 | slave1 |

| | 55 | amoeba |

+-+ +

3 rows in set (0.00 sec)

7. Add from Library 2 to amoeba7.1 configuration script dbServers.xml

# only need to configure the script dbServers.xml at this time

# cd / usr/local/amoeba/conf

# / usr/local/amoeba/bin/shutdown

# vi dbServers.xml

${defaultManager}

sixty-four

one hundred and twenty eight

3306

Shaw_db

User01

111111

five hundred

one

600000

True

192.168.12.56

192.168.12.55

192.168.12.56

3307

User01

111111

one

Slave1,slave2

7.2 Test master-slave

# / usr/local/amoeba/bin/launcher &

[1] 34792

[root@orcl conf] # 2018-03-06 16 Server listening on 12V 07142 INFO net.ServerableConnectionManager-Server listening on / 192.168.12.55