Summary of MySQL master-slave configuration _ MySQL

Source: Internet
Author: User
Summary of MySQL master-slave configuration

I. I have been working on MySQL master and slave databases for some time. I checked the disk space in the past two days and found that the disk in the database partition surged by more than 40 GB. I checked it all the way, it is found that the binlog has been over 40 GB since the master-slave replication is configured. the original source is here. check my. cnf. we can see that the size of binlog is 1 GB, but we can't see the deleted configuration. in MySQL, we show variables:

Mysql> show variables like '% log % ';


| Expire_logs_days | 0 |

The default value is 0, that is, logs does not expire. this is a global parameter, so you need to execute

Set global expire_logs_days = 8;

In this way, the log will be deleted eight days ago. if you need a reply, back up the log. However, this setting does not work. The next time you restart mysql, the configuration will be restored to the default value, therefore, you need. set in cnf,

Expire_logs_days = 8

In this way, restart is not afraid.

Now, in the production environment, set this time to 0, back up the mysql log file, and then manually clear the file.

To restore the previous data of the database, execute

Mysql> show binlog events;

As there is a large amount of data, it is very troublesome to view it. it takes half a day to open a file, so you should delete some unnecessary logs as appropriate.

And if it takes enough time, I will eat all my hard disk space.

1. log on to the system,/usr/bin/mysql

Use mysql to view logs:

Mysql> show binary logs;

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

| Log_name | File_size |

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

| Ablelee.20.01 | 150462942 |

| Ablelee.000002 | 120332942 |

| Ablelee.000003 | 141462942 |

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

2. delete the bin-log (before deleting ablelee.000003, but without ablelee.000003 ):

Mysql> purge binary logs to 'ablelee.000003'; Query OK, 0 rows affected (0.16 sec)

3. query results (only one record is available now ):

Mysql> show binlog events/G

* *************************** 1. row ***************************

Log_name: ablelee.000003

Pos: 4

Event_type: Format_desc

Server_id: 1

End_log_pos: 106

Info: Server ver: 5.1.26-rc-log, Binlog ver: 4

1 row in set (0.01 sec)

(Ablelee.000001 and ablelee.000002 have been deleted)

Mysql> show binary logs;

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

| Log_name | File_size |

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

| Ablelee.000003 | 106 |

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

1 row in set (0.00 sec)

(Other deleted formats are used !)

PURGE {MASTER | BINARY} logs to 'log _ name'

PURGE {MASTER | BINARY} logs before 'date'

Deletes all binary logs listed in the log index before the specified log or date. These logs will also be deleted from the list of records in the log index file, so that the given log becomes the first.

For example:

Purge master logs to 'MySQL-bin.010 ';

Purge master logs before '2017-06-22 13:00:00 ';

2. Currently, MySQL is used for databases of many projects. due to permission and other reasons, it is not convenient to deploy Nagios monitoring MySQL master-slave replication, therefore, I usually configure SHELL scripts on the slave machine to monitor the master/slave status of MySQL (set to run every ten minutes), and write the exact date into the error log every time a problem occurs, the script content is as follows:

#! /Bin/bash

# Check MySQL_Slave Status

# Crontab time

MYSQLPORT = 'netstat-na | grep "LISTEN" | grep "3306" | awk-F [: ""] + '{print $4 }''

MYSQLIP = 'ifconfig eth0 | grep "inet addr" | awk-F [: ""] + '{print $4 }''

STATUS = $ (/usr/local/webserver/mysql/bin/mysql-u yuhongchun-pyuhongchun101-S/tmp/mysql. sock-e "show slave status/G" | grep-I "running ")

IO_env = 'echo $ STATUS | grep IO | awk '{print $2 }''

SQL _env = 'echo $ STATUS | grep SQL | awk '{print $2 }''

If ["$ MYSQLPORT" = "3306"]


Echo "mysql is running"


Mail-s "warn! Server: $ MYSQLIP mysql is down "


If ["$ IO_env" = "Yes"-a "$ SQL _env" = "Yes"]


Echo "Slave is running! "


Echo "######## $ date ########">/data/check_mysql_slave.log

Echo "Slave is not running! ">/Data/check_mysql_slave.log

Mail-s "warn! $ MySQLIP_replicate_error " </data/check_mysql_slave.log


We recommend that you run the task every ten minutes.

*/10 * root/bin/sh/root/

Remember to assign a yuhongchun user to each MySQL instance from the machine. it doesn't matter if you have more permissions. you can only run them locally, as shown below:

Grant all privileges on *. * to "yuhongchun" @ "" identified by "yuhongchun101 ";

Grant all privileges on *. * to "yuhongchun" @ "localhost" identified by "yuhongchun101 ";

Script design ideas:

1. this script should be able to adapt to a variety of different intranet and internet environments, that is, environments with different IP addresses;

2. let the script also monitor whether MySQL runs normally;

3. set innodb_buffer_pool_size.

This parameter defines the maximum memory buffer size for table data and index data of the InnodDB storage engine. Unlike the MyISAM storage engine, MyISAM's key_buffer_size only caches index keys, while innodb_buffer_pool_size caches data blocks and index blocks at the same time. This feature is the same as that of Oracle, the higher the value, the less I/O required to access table data. On a dedicated database server, you can set this parameter to 80% of the physical memory of the machine. I usually configure it to 1/4 of the physical memory, for example, 8 GB memory production database, I usually configure it to about 2 GB.

4. I tested MySQL server load balancer for a long time, and finally integrated the opinions of old boys and other technical experts. Finally, I decided to use LVS + Keepalived as MySQL server load balancer, this is because when there are more than 10 backend machines, LVS has the best performance. if there are about 3-5 machines, HAProxy can easily handle the work.

5. everyone knows that disk I/O is always the performance bottleneck of the database. how should we select the proper RAID level in the production environment?

1. if data is frequently read/written and the reliability requirement is high, it is best to choose RAID10;

2. if data is frequently read and less written, you can choose RAID5 if you have certain reliability requirements;

3. if data is frequently read/written, but the reliability requirement is not high, select RAID0.

4. for master-slave database synchronization of core businesses, it is recommended to delay a period of time from the backup time of the machine. the common practice is to delay about one day.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.