On-Line system Architecture Design "database-master-slave"

Dual Master one from the architecture, from the server locally, for backup and development testing. Two online servers synchronize the database, ensure data consistency, and use xtrabackup Backup database + script to back up to the slave server 1 points a day.

One, the data of one primary server on the line is backed up and restored to the other two servers.

Innobackupex--defaults-file=/wqdata/mysql/my.cnf--user=bkuser--password= ' 123456 '/wqdata/mofidbbak/fullbackup/ Backup

Scp-p 8022 modb0909.tar.gz 223.100.98.83:/wqdata/Transmission

To recover a database from a server step:

Service Mysqld Stop
951 rm-rf/mydata/data/*
953 Innobackupex--defaults-file=/wqdata/mysql/my.cnf--copy-back/wqdata/2015-09-09_13-06-43/
955 Chown-r mysql.mysql/wqdata/mydata/data/
956 Service mysqld Start

Second, the configuration master-slave:

Master server Show master status;

Change from server master to master_host= ' 223.10.11.124 ', master_user= ' 1123wq ', master_password= ' 1112011 ', master_log_file= ' mysql-bin.000018 ', master_log_pos=24418754;

Start slave;

show slave status\g;

The Lord is doing it all over again.

Third, xtrabackup backup script, see previous article.

There are several high-availability scenarios for MySQL , each of which is used for informational purposes only:

A. normal master-slave replication ———— clients read/write to the database through master, slave as the standby machine, can be used for some queries, backup and other operations.

Advantages: Simple deployment, easy expansion, and the ability to provide certain data protection.

Disadvantage: If the master host hardware fails and cannot be recovered, it may result in some data loss that is not transferred to the slave end, and if some maintenance operations are required on the master side, after the slave is temporarily served as master, the master-slave environment needs to be re-built. will have a certain performance impact on the master.

B. Dual master replication ———— two MySQL servers to each other as their own master, as the other side of the slave to replicate, one end of the provision of write services, the other end of the service or just as a standby without providing any services, It is also capable of providing read services specifically to one or more slave.

Pros: The biggest benefit is that the primary master's write operation is not affected by the replication of the slave cluster, while the primary master does not have to be re-replication when it needs to switch.

Disadvantage: This architecture also has a disadvantage, that is, the backup master may become a bottleneck, because if the subsequent slave cluster is larger, the standby master may become a bottleneck because of excessive SLAVEIO thread requests.

C. Master-slave replication extension: read-write Separation ———— by a master copy to one or more slave schema mode, the client through the master to write to the database, through the slave side of the read operation, and can be backed up, master problems, You can switch the app to the slave end by moving it back. This scheme is mainly used in the application system with large reading pressure ratio.

Advantages: The structure is flexible, the database side is cheap to expand, can solve many small and medium-sized website database pressure bottleneck problem.

Disadvantage: Requires a program to achieve read and write separation, increase the complexity of the program, if the server architecture tuning or have a host failure, you also need to adjust the program.

D.hearbeat+ Dual master replication ———— The Hearbeat core consists of two parts: Heartbeat monitoring and resource takeover. Use Hearbeat+mysql Master synchronization to achieve high availability of MySQL database, when Master's host or hearbeat downtime will automatically switch to the standby, when the recovery can automatically switch back, Master continues to provide services.

Advantages: Simple configuration, to a certain extent, to avoid a single point of failure.

Cons: If MySQL on Master is hung, it cannot be detected and switched, and some scripts are required to assist with monitoring. By default, after switching to the standby machine, MySQL does not start automatically, requires manual or scripted startup, and is inconvenient to expand, which may cause problems with brain fissures.

E.Hearbeat+drbd+mysql———— This program uses hearbeat dual-machine hot standby software to ensure the high stability and continuity of the database, the data consistency has the DRBD tool to ensure. By default, only one MySQL is working, when the main MySQL server problems, the system will automatically switch to the standby to continue to provide services, when the primary database has been repaired, and the service will be cut back to continue to be serviced by the main MySQL.

Applicable scenario: For the database access is not too large, short-term traffic growth will not be too fast, the database availability is very high requirements of the scene.

Advantages: High security, high stability, high availability, automatic switching of failures.

Disadvantage: Only one server provides service, the cost is relatively high, inconvenient to expand, may appear the database brain fissure.

F.lvs+keepalive+ dual master replication ———— LVS provides load balancing and Keepalive as a failover. Server single-point write, read implementation load is very horizontal and failover. When the network, MySQL service, server, KeepAlive service failure, the server can automatically jump to the standby machine, when the primary server service starts up automatically switch back.

Application scenario: For scenarios where database availability requirements are high, the read-pressure ratio is large (the backend can be followed by one or more slave servers, allowing LVS to achieve read load balancing). This scheme can also be easily managed and maintained on a single database, as well as switching tasks, such as table structure changes for large tables, database upgrades, etc.

Advantages: High availability efficiency, can be based on the service and system availability of various aspects of switching. You can set both the write VIP and the read VIP separately to prepare for the read-write separation. Extension is convenient. You can add multiple slave servers at a later time and do read load balancing.

Disadvantage: If you want to implement the specified condition substitution or not replace after starting or recovering, you need to implement it in other ways, such as temporarily changing the port of MySQL. The installation configuration is slightly more complex than a single write and requires another VIP. Management is more complex than single writes. After the main switch, manually switch from the need. Primary and standby switching takes about 1s of time.

G.mmm+mysqlproxy+ dual master replication ———— MMM (MySQL master replication Manager) is a flexible set of scripts to monitor and failover MySQL replication? and manage MySQL Master-master the replication configuration. The accompanying toolkit enables read load balancing for multiple slaves. Two MySQL server servers are mainly from, MMM provides the function of floating IP, if the current primary server hangs, the client's read and write requests will be automatically transferred to another server through the virtual IP drift, Thus, the failover of the server is realized automatically, and the Mysqlproxy realizes the read and write separation.

Application scenario: This scheme is a more mature solution, which is suitable for the scenario of large database access and fast business growth. Suitable for web2.0 applications where read/write is high.

Advantages: Security, high stability, good scalability, high availability, when the primary server hangs, another master immediately take over, the other from the server can automatically switch, without manual intervention.

Disadvantage: At least three nodes, the number of host requirements, for a high-real-time situation may need to do some processing.

Mysqlprox and MySQL mmm integration of the necessity: to achieve the MySQL database layer load Balancing, database node implementation ha dynamic switch, read and write separation, reduce the primary database load

2 ways to solve MySQL master never sync.

First on the Master library:

Mysql>show processlist; See if the next process is too much sleep. Found to be normal.

Show master status; also normal.

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|     mysqld-bin.000001 |              3260 | | Mysql,test,information_schema |

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

1 row in Set (0.00 sec)

Go to the slave and check it out.

Mysql> Show Slave Status\g

Slave_io_running:yes

Slave_sql_running:no

Visible is slave different steps

Two workarounds are described below:

Method one: After ignoring the error, continue syncing

This method is applicable to the case that the data of master-slave database is not very different, or the data can not be completely unified, the data requirements are not strict.

Solve:

Stop slave;

#表示跳过一步错误, the following numbers are variable

Set global sql_slave_skip_counter = 1;

Start slave;

Then use mysql> Show slave status\g to view:

Slave_io_running:yes

Slave_sql_running:yes

OK, now the master-slave synchronization state is normal ...

Mode two: Re-master from, fully synchronized

This method is suitable for the case that the master-slave database data is large or the data is completely unified.

The steps to resolve are as follows:

1. Advanced Master Library, lock table, prevent data write

Use the command:

mysql> flush tables with read lock;

Note: This is locked to a read-only state, and the statement is case insensitive

2. Perform data backup

#把数据备份到mysql. bak.sql file

[Email protected] mysql]#mysqldump-uroot-p-hlocalhost > Mysql.bak.sql

Note here: Database backups must be done on a regular basis, with shell scripts or Python scripts, to make sure the data is foolproof

3. View Master Status

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|     mysqld-bin.000001 |              3260 | | Mysql,test,information_schema |

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

1 row in Set (0.00 sec)

4. Upload the MySQL backup file to the slave library for data recovery

#使用scp命令

[Email protected] mysql]# SCP mysql.bak.sql [email protected]:/tmp/

5. Stop the state from the library

mysql> stop slave;

6. Then run the MySQL command from the library, import the data backup

mysql> Source/tmp/mysql.bak.sql

7. Set synchronization from the library, note that the synchronization point is in the main library show Master status information | file| Position two items

Change Master to Master_host = ' 192.168.128.100 ', master_user = ' rsync ', master_port=3306, master_password= ', Master_log _file = ' mysqld-bin.000001 ', master_log_pos=3260;

8. Re-open from sync

mysql> start slave;

9. View sync Status

Mysql> Show slave status\g View:

Slave_io_running:yes

Slave_sql_running:yes

10. Unlock the main library table

mysql> UNLOCK TABLES;

On-Line system Architecture Design "database-master-slave"