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

Let me tell you briefly about the specific process of building a dual-host MM+keepalived high-availability architecture for MySQL. Have you learned about similar topics before? If you are interested, let's take a look at this article. I believe it will be of some help to you after reading the specific process of building a dual-host MM+keepalived high-availability architecture for MySQL.

More common high-availability architectures include MHA,MM,PXC and so on.

The advantages of the MM architecture are:

The construction is fast and convenient, suitable for small and medium-sized companies, and MySQL is the master and slave of each other to ensure the consistency of the two databases. Keepalived implements virtual IP and automatic service monitoring functions, and uses VIP automatic switching to ensure high availability, which can basically meet business needs.

But there are the following problems with this architecture:

The main results are as follows: 1 the problems of double vip and brain fissure after handover can be solved by adding arbitration nodes.

If you lose data when switching, you can use script to monitor binlog logs or use version 5.7 enhanced semi-synchronization.

(3) Master-slave delay can not be avoided. If there is no master-slave delay, PXC architecture can be adopted.

4 when the failed CVM is restored, it cannot be switched back automatically, so it is troublesome to switch back, and you need to manually modify the weight in the configuration file.

5 there is also a big test of the ability to write scripts.

Children's shoes that want to understand the working principle and configuration file description of keepalived can be made on their own Baidu, or take a look at this very detailed article, Portal keepalived working principle and configuration document description

Most of the company's architecture is MM+keepalived or MM+heartbeat, and then the slave library is hung under the main library as the read library, and together with the mycat cluster to achieve read-write separation and sub-database sub-table is a relatively complete set of MySQL high-availability architecture (see the following figure).

Now there is a new business needs a new environment, here to record the deployment process, hope to help students who have just come into contact with MySQL, the previous MySQL installation and master-slave construction can not be seen, not much nonsense, the architecture diagram is as follows

One environment preparation

The operating system version is Ubuntu 14.04.5

The CVM stores CPU 1T data directory with 16 GB of memory and 4 cores.

The database version is 5.6.39-log

IP planning

Master1: 192.168.70.154

Master2: 192.168.70.156

Vip 192.168.70.157

Two MySQL double main frame

On two database cloud servers

Upload installation package

Mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz

Decompression

Tar-zxvf mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz

Move the unzipped package to MySQL's basedir

Mv mysql-5.6.39-linux-glibc2.12-x86_64 / usr/local/mysql

Create mysql groups and users

Groupadd mysql

Useradd-r-m-s / bin/bash-g mysql mysql

Modify the permissions of related directories

Chown-R mysql:mysql / usr/local/mysql/

Chown-R mysql:mysql / data/mysql

Chown-R mysql:mysql / etc/my.cnf

Edit configuration file

The configuration file needs to pay special attention to:

1 the server-id must be different, otherwise the master-slave build reports an error.

2 primary key self-increasing parameter, which can avoid the error of primary key conflict

The master1 settings are as follows

Auto-increment-increment = 2

Auto-increment-offset = 1

The master 2 settings are as follows

Auto-increment-increment = 2

Auto-increment-offset = 2

The following is a comprehensive one that can be used as a reference, and the others should be modified according to your own actual situation:

Vim / etc/my.cnf

[client] # password = [your_password] port = 3306socket = / tmp/mysql.sock# set client character set default-character-set = utf8 [mysqld] # generic configuration optionsport = 3306socket = / tmp/mysql.sockbasedir = / usr/local/mysqldatadir = / data/mysqluser = mysql# set CVM time zone It is recommended to display the specified default-time-zone='+8:00'# setting CVM character set every time. Note that like the client character set, it is recommended to set utf8 to prevent Chinese garbled character-set-server=utf8skip-name-resolve# from supporting federated distributed engine. By default, # federated# setting sql check mode is not supported. This setting affects how strictly the database verifies the field data # sql_mode = STRICT_TRANS_TABLESexpire_logs_days = "this should be paid extra attention in synchronous configuration, otherwise it will easily lead to primary key conflicts auto-increment-increment = 2auto-increment-offset = 1back_log = 10000max_connect_errors = 100000table_open_cache = 1024#external-lockingmax_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Mread_buffer. _ size = 2Mread_rnd_buffer_size = 4Msort_buffer_size = 8Mjoin_buffer_size = 4Mthread_cache_size = 6percent this variable is for Solaris systems If you set this variable, mysqld calls thr_setconcurrency (). This function enables the application to provide the desired number of threads to a threaded system running at the same time. Thread_concurrency = "whether there is a misam table or not, it is recommended to 16-128m. If there are many misam tables, it is recommended to set it to 128m. Do not exceed # at most. 256M#query_cache_size = 64M#query_cache_limit = 2Mft_min_word_len = 4#memlock#5.5 below the recommended display setting." 5.5 the default is innodbdefault-storage-engine = innodbthread_stack = 192K# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLEtransaction_isolation = REPEATABLE-READtmp_table_size = 64M# to prevent confusion in the update of the library. The recommended setting in synchronization # replicate_wild_do_table=tang_monitor_report.%replicate_wild_do_table=%.%# recommends displaying the names of the specified binlog and relaylog to prevent errors in log-bin=mysql-binrelay-log=mysql-relay-bin#binlog record format caused by modifying hostname It is recommended to set row to prevent the problem that the data caused by the statement format cannot be detected all the time. Whether the binlog_format=mixed# setting writes updates to the main library to binlog, which is generally closed. It is recommended to turn off log_slave_updates#log_warningsslow_query_logslow_query_log_file=/data/mysql/myslow.loglong_query_time = 2innodb_buffer_pool_instances=4innodb_change_buffering = changesinnodb_old_blocks_time=1000innodb_autoextend_increment=50sync_binlog=100innodb_open_files=1024innodb_file_per_table = 1open-files-limit = 8192log-error = / data/mysql/error.logserver-id = 330615 semi-synchronous configuration parameters. It's very simple, isn't it? Simple and shocking # rpl_semi_sync_slave_enabled=1#rpl_semi_sync_master_enabled=1#rpl_semi_sync_master_timeout=1000 # slave library is useful. Set whether the slave library is read-only. It is recommended to turn on # read_only#misam index caching if many misam tables are recommended to increase key_buffer_size = 128Mbulk_insert_buffer_size = 64M# when sorting the buffer of MyISAM index # allocation during REPAIR TABLE or CREATE INDEX index creation or ALTER TABLE. Myisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10G# set the number of threads repaired by the misam table myisam_repair_threads = 1#myisam table automatic crash recovery level myisam_recover= BACKUP,FORCE # set the cache of the innodb data dictionary, generally 16-20m can basically If there are too many innodb tables, you can appropriately increase to # 32Minnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 10Ginnodb_data_file_path = ibdata1:512M:autoextend#5.1 version (innodb_file_io_threads) and previous versions are hard-coded to 4 The default is 4, and it is useless to modify it after 5.5. the maximum # can be modified no more than 64innodb_write_io_threads = 8innodb_read_io_threads = 8#innodb_force_recovery=1# set the number of concurrency of innodb internal threads innodb_thread_concurrency = setting log refresh mode innodb_flush_log_at_trx_commit = setting binlog refresh mode, 1 indicates that binary was written before commit, but transaction commit failed. Binary logs cannot be rolled back # rollback: set innodb_support_xa=1 (enabled by default) to ensure the synchronization of binary logs and data files # sync_binlog = 1#innodb_fast_shutdowninnodb_log_buffer_size = 4Minnodb_log_file_size = 256Minnodb_log_files_in_group = "configure the ratio of dirty leaves of data innodb_max_dirty_pages_pct = 7" set the mysql data refresh mode by default is fdatasync# doublewrite flush logic. The default value is "fdatasync", another# option is "O_DSYNC" .innodb _ flush_method=O_DSYNCinnodb_lock_wait_timeout = permission controls the permissions of ordinary users show database # skip-show-database# controls whether UDFlog_bin_trust_function_creators is supported, controls whether slave threads restart with the restart of mysql server, it is recommended to roll back the current query before # skip_slave_start # #, and now roll back the whole thing This allows faster release of resources innodb_rollback_on_timeout = ON # to set the connection timeout. If you do not want persistent connections, you can set a relatively small value. A master Wang suggested setting it to 10-15interactive_timeout = 300#wait_timeout only works on threads of TCP/IP and Socket links. The general setting value is the same as interactive_timeout wait_timeout = 300 connections # used for new connections In systems with high concurrency, it is recommended that 10-15 default 10connect_timeout=15#### master-slave replication time before slave checks whether the master database is normal and attempts to reconnect. Default 1 hour recommended 30 seconds slave_net_timeout = 3 "database replicate-ignore-db=performance_schemareplicate-ignore-db=information_schemareplicate-ignore-db=testreplicate-ignore-db=mysql that does not replicate [ Mysqldump] # Do not buffer the whole result set in memory before writing it to# file. Required for dumping very large tablesquickmax_allowed_packet = 16m [MySQL] no-auto-rehash# Only allow UPDATEs and DELETEs that use keys.#safe- updates [myisamchk] key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8m

Initialization begins after the above configuration is completed

Mysql_install_db-basedir=/usr/local/mysql-datadir=/data/mysql-defaults-file=/etc/my.cnf

A message asking you to change your root password indicates that the initialization is successful. If not, it may prompt you to install related dependent packages.

Start

Mysqld_safe-- defaults-file=/etc/my.cnf &

You can verify whether the installation is successful by checking whether the MySQL process exists, port 3306 is listening, and so on.

Ps-ef | grep mysql

Netstat-nat | grep 3306

Configure the main main architecture

154,156 (master)-156 (slave) are built as follows

On 154

Create replication user

Grant replication slave on *. * to 'repl'@'192.168.70.156' dentified by' mysql'

Flush privileges

View log number and offset

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

On 156

CHANGE MASTER TOMASTER_HOST='192.168.70.154',MASTER_USER='repl',MASTER_PASSWORD='mysql',MASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=1106

Start slave

Show slave status\ G

Both synchronization processes indicate to yes that the master-slave replication was successful.

Slave_IO_Running: YesSlave_SQL_Running: Yes

15615 (master)-154 (slave) is the same.

At this point, the master-slave construction is complete.

Three keepalived installation configuration

Install directly by apt-get

Apt-get-y install keepalived

Modify configuration file 154

Vim / etc/keepalived/keepalived.conf

! Configuration File forkeepalivedvrrp_instance VI_154 {state BACKUP # both set the BACKUP interface eth0 # network card virtual_router_id 157# with the same master and standby, and the keepalived router_id that is not a pair in the same local area network cannot be the same, otherwise an error will be reported. Here the priority of 157advert_int # is used, and the backup setting of 90 advert_int 1 nopreempt # does not actively preempt resources, only in the high priority setting of master. Backup does not set authentication {auth_type PASS auth_pass 1111} virtual_ipaddress {192.168.70.157}} virtual_server 192.168.70.157 3306 {delay_loop 2 # lb_algo rr # LVS algorithm, so we turn off # lb_kind DR # LVS mode, if not Standby CVM cannot connect to primary MySQL persistence_timeout 50 # connection to the same IP via VIP within 60 seconds is assigned to the same real CVM protocol TCP real_server 192.168.70.154 3306 {# detect local mysql,backup also write to detect local mysql weight 3 notify_down / etc/keepalived/mysql.sh # execute this script when mysq serves down Kill keepalived to switch TCP_CHECK {connect_timeout 3 # connection timeout nb_get_retry 3 # retry number delay_before_retry 3 # retry interval}} ~

The configuration on 156 is as follows

! Configuration File forkeepalivedvrrp_instance VI_156 {state BACKUP # both set BACKUP interface eth0 virtual_router_id 157s with the same priority 90 # priority, backup sets 90 advert_int 1 nopreempt #, does not actively preempt resources, only sets master with high priority. Backup does not set authentication {auth_type PASS auth_pass 1111} virtual_ipaddress {192.168.70.157}} virtual_server 192.168.70.157 3306 {delay_loop 2 # lb_algo rr # LVS algorithm, so we turn off # lb_kind DR # LVS mode, if not Standby CVM cannot connect to primary MySQL persistence_timeout 50 # connection to the same IP via VIP within 60 seconds is assigned to the same real CVM protocol TCP real_server 192.168.70.156 3306 {# detect local mysql,backup also write to detect local mysql weight 3 notify_down / etc/keepalived/mysql.sh # execute this script when mysq serves down Kill keepalived to switch TCP_CHECK {connect_timeout 3 # connection timeout nb_get_retry 3 # retry number delay_before_retry 3 # retry interval}}

Write a script, which is used to kill the keepalived process and achieve VIP drift when the mysql service is not detected

Vim / etc/keepalived/mysql.sh

#! / bin/bash

Pkill keepalived

Grant executive authority

Chmod + x / etc/keepalived/mysql.sh

Start

/ etc/init.d/keepalived start

View the log

The ubuntu system log is in / var/log/syslog

The centos system log is in / var/log/message

Startup log on 154

Startup log on 156

Vip appears on 154,

Four verifications

Simulate 154 CVM database downtime

Root@qsbilldatahis-db01:~# mysqladmin-uroot-p shutdownEnter password:

View the log

Keepalived can not check the mysql service, change the status to fail, change the virtual IP remove, execute the script, and kill the keepalived process.

The view log on 156changes to master status

Ip addr show

Vip is already on 156. using navicate tools to connect to VIP can still provide services normally.

At this point, everything has been built.

How is it safest for VIP to switch back?

Now simulate 154and let VIP drift back to 154from 156. Now start the database on 154

Root@qsbilldatahis-db01:~# mysqld_safe-- defaults-file=/etc/my.cnf & [1] 592root@qsbilldatahis-db01:~# 180411 17:24:49 mysqld_safe Logging to'/ data/mysql/error.log'.180411 17:24:49 mysqld_safe Starting mysqld daemon with databases from / data/mysql

Start keepalived on 154s

Oot@qsbilldatahis-db01:~# / etc/init.d/keepalived start * Starting keepalived keepalived [OK]

Stop the keepalived,vip on 156and you can float back to 154th.

/ etc/init.d/keepalived stop

This completes the switch back.

What do you think of the specific process of building a dual-host MM+keepalived high-availability architecture for MySQL? what do you think of this article? does it yield anything? If you want to know more about it, you can continue to follow our industry information section.

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.