Summary of MySQL master-slave Configuration

BKJIA exclusive Article]I,I have been doing MySQL Master/Slave 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:

Author's blog: andrewyu.blog.51cto.com

 
 

  
  
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.000001 | 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 '2008-06-22 13:00:00'; 
 
 

II,Currently, MySQL is used for databases of many projects. Due to permissions 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 00:10 
  
  
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/data/check_mysql_slave.log 
  
  
echo "Slave is not running!" >> /data/data/check_mysql_slave.log 
  
  
mail -s "warn! $MySQLIP_replicate_error" yuhongchun027@163.com << /data/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;

III,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.

IV,I tested MySQL Load Balancing for a long period of time, and finally integrated the opinions of old boys and other technical experts, and finally decided to use LVS + Keepalived as MySQL load balancing, 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.

V,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.

Introduction:

Yu hongchun fuqin, author of "building high-availability Linux servers", yipai System Architect, Senior Project management engineer, ChinaUnix cluster, and high-availability version moderator.