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

In this issue, the editor will bring you the solution to the single point of failure of the main library of MySQL dual-main architecture. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

In the enterprise, the bottleneck of the general system architecture will appear in this part of the database, Mysql master-slave architecture to a large extent to solve this bottleneck, but there are also many problems in the Mysql master-slave synchronization architecture. For example:

1. It is often difficult to expand the data writing part (that is, the main database), although many large companies split the data in terms of logical business. for example, commodity inventory is split by region (one inventory in an area is a main database, and then synchronize the total inventory regularly), and divided according to the type of goods (a set of databases for a type of goods). But it is difficult for many small and medium-sized companies to achieve.

two。 Master-slave synchronization is generally a master library, once there is a problem with the master database, it may directly lead to the collapse of the entire master-slave synchronization architecture, although it can be slowly recovered after discovery, but this recovery time is unacceptable for many companies.

Today's blog post mainly provides an idea to solve the problem of single point failure of the main database:

A main library (we call it master-01) provides services and is only responsible for writing data

Take a database server (we call it Master-02) as the slave library of the master-01 master library (master-slave synchronization between them)

For high availability between the two main libraries, you can use solutions such as keepalived (be sure to ensure that master-01 is also the master of keepalived)

The program writes the highly available VIP address at the place where the main library IP address is called

All slave servers that provide services synchronize with master-02

It is recommended that when the high availability strategy is adopted, when master-01 has problems switching to master-02, even if master-01 is restored, do not let it automatically accept VIP addresses, otherwise it may cause data mixing.

This can ensure the high availability of the master database to a certain extent. After the down of one master library is dropped, you can switch to another master database in a very short time (to minimize the impact on the business caused by the downtime of the master database), thus reducing the pressure caused by master-slave synchronization to the online master database. However, there are several shortcomings:

Master-02 may be idle all the time (in fact, it can assume the role of part of the library to be responsible for part of the query request)

In this way, the slave library that really provides the service will have to wait for master-02 to synchronize the data first before going to master-02 to synchronize the data, which may result in a certain increase in synchronization delay.

If master- 01 returns to normal, will it cause data write confusion (this can set the response rule in keepalived so that it does not "seize power", we think it is OK to adjust the operation.

The simple diagram of the architecture is as follows:

Specific implementation plan:

First, install MySQL services on all servers that need to provide services (recommended source code installation)

1. Yum installation dependency package

Yum-y install cmake make gcc gcc-c++ ncurses-devel bison openssl-devel

2. Add the users / groups required by MySQL

Groupadd mysql useradd-g mysql-r mysql

3. Download the MySQL source code package

Wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.36.tar.gz

4. Create the directory required for the MySQL installation

Mkdir / data/mydata/ {data,tmp,logs}-pv

5. Decompress, compile and install MySQL

Tar xf mysql-5.5.36.tar.gz cd mysql-5.5.36 cmake. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql\-DMYSQL_DATADIR=/data/mydata/data\-DSYSCINFDIR=/etc\-DWITH_INNOBASE_STORAGE_ENGINE=1\-DWITH_ARCHIVE_STORAGE_ENGINE=1\-DWITH_BLACKHOLE_STORAGE_ENGINE=1\-DWITH_READLINE=1\-DWITH_SSL=system\-DWITH_ZLIB=system\-DWITH_LIBWARP=0\-DWITH_UNIX _ ADDR=/tmp/mysql.sock\-DDEFAULT_CHARASET=uft8\-DDEFAULT_COLLATTON=utf9_general_ci\ make & & make install

6. Provide startup script for MySQL

Cp support-files/mysql.server / etc/rc.d/init.d/mysqld

7. Provide the configuration file for the master-01 main library (the configuration file with 32 GB of memory is conservative (full connection takes up about 25 GB of memory).

[client] port = 3306 socket = / var/lib/mysql/mysql.sock default-character-set = utf-8 [mysqld] server-id = 1 port = 3306 user = mysql basedir = / usr/local/mysql datadir = / data/mydata/data tmpdir = / data/mydata/tmp socket = / var/lib/mysql/mysql.sock skip-external-locking skip-name-resolve default-storage-engine = INNODB character-set-server = utf8 wait-timeout = 100 connect_timeout = 20 interactive_timeout = 100 back_log = 300 myisam_recover event_scheduler = on log-bin=/data/mydata/logs/mysql-bin binlog_format = row max_binlog_size = 64m binlog_cache_size = 1m slave-net-timeout = 10 skip-slave-start slow_query_log = 1 long_query_time = 1 slow_query_log_file = / data/mydata/mysqllog/logs/mysql.slow log-error = / data/mydata/mysqllog/logs/error.log max_connections = 1000 max_user_connections = 1000 max_connect_errors = 10000 key_buffer_size = 32m # MyISAM-based server To increase this value max_allowed_packet = 64m table_cache = 4096 table_open_cache = 4096 table_definition_cache = 4096 sort_buffer_size = 512K read_buffer_size = 512K read_rnd_buffer_size = 512K join_buffer_size = 512K tmp_table_size = 64m max_heap_table_size = 64m query_cache_type = 0 query_cache_size = 0 bulk_insert_buffer_size = 16m thread_cache_size = 64 Thread_concurrency = 16 # CPU core number * 2 thread_stack = 256K innodb_data_home_dir = / data/mydata/data innodb_log_group_home_dir = / data/mydata/mysqllog/logs innodb_data_file_path = ibdata1:1G:autoextend innodb_buffer_pool_size = 16G innodb_buffer_pool_instances = 4 innodb_additional_mem_pool_size = 16m innodb_log_file_size = 512m innodb_log_buffer_size = 32m innodb_ Log_files_in_group = 3 innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 10 innodb_sync_spin_loops = 40 innodb_max_dirty_pages_pct = 90 innodb_support_xa = 1 innodb_thread_concurrency = 0 innodb_thread_sleep_delay = 500 innodb_file_io_threads = 4 innodb_concurrency_tickets = 1000 log_bin_trust_function_creators = 1 innodb_flush_method = O_DIRECT innodb_file_per_table # whether to use single form space innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_io_capacity = 1000 innodb_file_format = Barracuda # do not open single form space This option is not valid innodb_purge_threads = 1 innodb_purge_batch_size = 32 innodb_old_blocks_pct = 75 innodb_change_buffering = all transaction_isolation = READ-COMMITTED [mysqldump] quick max_allowed_packet = 32m [mysql] no-auto-rehash [myisamchk] key_buffer_size = 64m sort_buffer_size = 256m read_buffer = 2m write_buffer = 2m [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 10240

8. Provide configuration files for master-02

The configuration file for master-02 only needs to be slightly modified on master-01 server-id= 20 log_slave_updates = 1 # add (write replication events to binlog, a server acts as both master and slave libraries this option must be turned on) replicate-same-server-id=0 # add (prevent MySQL cyclic updates) relay_log_recovery = 1 # add (automatic repair of MySQLrelay_log)

9. Provide configuration file for slave library (8G)

[client] port = 3306 socket = / var/lib/mysql/mysql.sock default-character-set = utf8 [mysqld] server-id = 2 port = 3306 user = mysql basedir = / usr/local/mysql datadir = / data/mydata/data tmpdir = / data/mydata/tmp socket = / var/lib/mysql/mysql.sock skip-external-locking skip-name-resolve default-storage-engine = INNODB character-set-server = utf8 wait-timeout = 100 connect_timeout = 20 interactive_timeout = 100 back_log = 300 myisam _ recover event_scheduler = on log-bin=/data/mydata/logs/mysql-bin binlog_format = row max_binlog_size = 64m binlog_cache_size = 1m slave-net-timeout = 10 relay_log_recovery = 1 slow_query_log = 1 long_query_time = 1 slow_query_log_file = / data/mydata/mysqllog/logs/mysql.slow log-error = / data/mydata/mysqllog/logs/error.log max_connections = 500 max_user_connections = 500 max_connect_errors = 10000 key_buffer_size = 32m # MyISAM-based server To increase this value max_allowed_packet = 64m table_cache = 2048 table_open_cache = 2048 table_definition_cache = 2048 sort_buffer_size = 128K read_buffer_size = 128K read_rnd_buffer_size = 128K join_buffer_size = 128K tmp_table_size = 16m max_heap_table_size = 16m query_cache_type = 0 query_cache_size = 0 bulk_insert_buffer_size = 16m thread_cache_size = 64 Thread_concurrency = 4 # CPU core * 2 thread_stack = 128K innodb_data_home_dir = / data/mydata/data innodb_log_group_home_dir = / data/mydata/mysqllog/logs innodb_data_file_path = ibdata1:1G:autoextend innodb_buffer_pool_size = 2G innodb_buffer_pool_instances = 4 innodb_additional_mem_pool_size = 4m innodb_log_file_size = 512m innodb_log_buffer_size = 16m innodb_ Log_files_in_group = 3 innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 10 innodb_sync_spin_loops = 40 innodb_max_dirty_pages_pct = 90 innodb_support_xa = 1 innodb_thread_concurrency = 0 innodb_thread_sleep_delay = 500 innodb_file_io_threads = 4 innodb_concurrency_tickets = 1000 log_bin_trust_function_creators = 1 innodb_flush_method = O_DIRECT innodb_file_per_table # whether to use single form space innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_io_capacity = 1000 innodb_file_format = Barracuda # do not open single form space This option is not valid innodb_purge_threads = 1 innodb_purge_batch_size = 32 innodb_old_blocks_pct = 75 innodb_change_buffering = all transaction_isolation = READ-COMMITTED [mysqldump] quick max_allowed_packet = 32m [mysql] no-auto-rehash [myisamchk] key_buffer_size = 64m sort_buffer_size = 256m read_buffer = 2m write_buffer = 2m [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 10240

10. Initialize MySQL

/ usr/local/mysql/scripts/mysql_install_db-user=mysql-datadir=/data/mydata/data/-basedir=/usr/local/mysql

11. Give executable permissions to the startup script and start MySQL

Chmod + x / etc/rc.d/init.d/mysqld / etc/init.d/mysqld start

2. Configure master-01

1. Add master-slave synchronization account

Mysql > grant replication slave on *. * to 'repl'@'192.168.237.%' idetified by' 123456; mysql > flush privileges

2. Check the status of the main library

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +- -+-+ | mysql-bin.000009 | 652 | +- -+ 1 row in set (0.01 sec)

3. Because this is a test environment, you can ensure that no data is written, otherwise you need to lock the table-- > View status-- > backup data-- > View status (make sure there is no change)-> unlock the table.

3. Configure master-02

1. Configure the slave whose master-02 is master-01.

# if you have data online, you need to import data mysql > CHANGE MASTER TO-> MASTER_HOST='192.168.237.128',-> MASTER_PORT=3306,-> MASTER_USER='repl',-> MASTER_PASSWORD='123456',-> MASTER_LOG_FILE='mysql-bin.000009',-> MASTER_LOG_POS=652; Query OK, 0 rows affected (0.03 sec) mysql > start slave Mysql > show slave status\ G Slave_IO_Running: Yes # make sure it is yes Slave_SQL_Running: Yes # make sure it is yes

2. Configure synchronous users of master-02

Mysql > grant replication slave on *. * to 'repl'@'192.168.237.%' identified by' 123456; mysql > flush privileges

3. Check the status of master-02

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +- -+-+ | mysql-bin.000004 | 689 | +- -+ 1 row in set (0.00 sec)

Fourth, the slave library can be configured as a master-02 slave according to the above steps (in order to save space, we will not repeat them one by one)

Create a database on master-01 to test the synchronization effect

6. Go to master-02 and follow the database to see if the data has been synchronized

All right, so the data synchronization is complete.

The above is the solution to the single point of failure of the main library of the MySQL dual main architecture shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.