Summary of MySQL master-slave configuration _ MySQL

Summary of MySQL master-slave configuration bitsCN.com

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 % ';

Found,

| 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"]

Then

Echo "mysql is running"

Else

Mail-s "warn! Server: $ MYSQLIP mysql is down "yuhongchun027@163.com

Fi

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

Then

Echo "Slave is running! "

Else

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

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

Mail-s "warn! $ MySQLIP_replicate_error "yuhongchun027@163.com </data/check_mysql_slave.log

Fi

We recommend that you run the task every ten minutes.

*/10 * root/bin/sh/root/mysql_slave.sh

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" @ "127.0.0.1" 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.

BitsCN.com