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

This article mainly gives you a brief description of what are the norms for managing BlueKing's MySQL database. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article can bring you some practical help in managing BlueKing's MySQL database.

MySQL is the main database for BlueKing platform to access data, its stability is related to the experience of BlueKing platform, and its data security may be related to the information related to enterprise IT assets, which should be paid enough attention to in the process of installing and maintaining BlueKing platform. This paper will analyze the following problems and provide suggestions to deal with them. These problems are: upgrade, configure automatic log cleaning, binlog manual cleaning and maintenance, table emptying, backup.

The MySQL database of BlueKing platform is automatically installed when the platform is installed. Its version is 5.5.24, and the architecture is replicated as master and slave (in the case of 5 CVMs). The problems mentioned below are described below.

MySQL upgrade

1. Analysis of problems

The default MySQL version installed on BlueKing platform is 5.5.24, which generally does not meet the requirements of the security scan version. In order to avoid re-upgrading the database due to security problems during use, it is recommended to upgrade the database in time after completing the installation of the platform. At this time, the database has not yet been stored in business data, and the upgrade is risk-free and efficient, based on the upgrade experience that has been verified in the generation environment. In order to prevent the compatibility problems caused by the excessive span of the version, it is recommended to upgrade to the highest version of MySQL5.5, that is, 5.5.62. use the method of logical upgrade, use mysqldump to export the whole database of the lower version, and then import it into the installed new version.

two。 Logical upgrade process

1. Check the current MySQL process

Check the process details of the currently running MySQL, mainly to view the startup parameters for post-upgrade comparison

[root@paas-1 install] # ps-ef | grep mysql

Root 20407 8526 0 15:10 pts/3 00:00:00 grep-color=auto mysql

Root 29942 10 Dec17? 00:00:00 / bin/sh / data/bkce/service/mysql/bin/mysqld_safe-- datadir=/data/bkce/public/mysql/-- pid-file=/data/bkce/public/mysql/mysql.pid

Mysql 30344 29942 5 Dec17? 13:17:37 / data/bkce/service/mysql/bin/mysqld-basedir=/data/bkce/service/mysql-datadir=/data/bkce/public/mysql/-plugin-dir=/data/bkce/service/mysql/lib/plugin-user=mysql-log-error=/data/bkce/public/mysql//paas-1.err-pid-file=/data/bkce/public/mysql/mysql.pid-socket=/data/bkce/logs/mysql/mysql.sock-port=3306

2. Full database backup

Use the following command to back up the full database, and the backup path can be adjusted according to the amount of data, disk performance, etc.

[root@paas-1 service] # mysqldump-uroot-x-A-E-R > / tmp/alldbback.sql

3. Back up the installation directory and data directory

Stop the database service and back up the basedir,datadir directory. Be sure to back up before upgrading to avoid accidental fallback.

[root@paas-1 service] # mkdir / data/backup

[root@paas-1 service] # mv mysql/ / data/backup/

[root@paas-1 service] # cd / data/bkce/public/

[root@paas-1 public] # mv mysql / data/backup/mysql_data

4. Decompress and install the new version

Use the binary package to decompress and install, download the 5.5.62 package, upload it to the installation directory for decompression, and create a soft connection as mysql

[root@paas-1 service] # tar zxf / tmp/mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz

[root@paas-1 service] # ln-s mysql-5.5.62-linux-glibc2.12-x86_64/ mysql

5. Initialize the database

Initialize the data directory

[root@paas-1 public] # mkdir mysql

[root@paas-1 mysql] # cd / data/bkce/service/mysql

[root@paas-1 mysql] # / s/mysql_install_db-- user=mysql-- basedir=/data/bkce/service/mysql-- datadir=/data/bkce/public/mysql/

Installing MySQL system tables...

181227 15:39:10 [Note] Ignoring-- secure-file-priv value as server is running with-- bootstrap.

181227 15:39:10 [Note] / data/bkce/service/mysql/bin/mysqld (mysqld 5.5.62-log) starting as process 24766.

OK

Filling help tables...

181227 15:39:11 [Note] Ignoring-- secure-file-priv value as server is running with-- bootstrap.

181227 15:39:11 [Note] / data/bkce/service/mysql/bin/mysqld (mysqld 5.5.62-log) starting as process 24790.

OK

To start mysqld at boot time you have to copy

Support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER!

To do so, start the server, then issue the following commands:

/ data/bkce/service/mysql/bin/mysqladmin-u root password' new-password'

/ data/bkce/service/mysql/bin/mysqladmin-u root-h 132.108.252.43 password' new-password'

Alternatively you can run:

/ data/bkce/service/mysql/bin/mysql_secure_installation

Which will also give you the option of removing the test

Databases and anonymous user created by default. This is

Strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

Cd / data/bkce/service/mysql; / data/bkce/service/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

Cd / data/bkce/service/mysql/mysql-test; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

6. Modify the configuration and start

Since pid file is not set in the original my.cnf configuration file, the new version will be automatically generated according to the host name when it starts, which is not consistent with the pid file under the original process. You need to add the following configuration to the configuration file:

Pid-files=

Vim / etc/my.cnf

[mysqld]

/ data/bkce/public/mysql/mysql.pid

Start the database:

/ data/bkce/service/mysql/bin/mysqld_safe &

7. Import data

Log in to mysql, confirm that the version has been upgraded to 5.5.62, and then import the backup data

Mysql-uroot set global expire_logs_days=7

Setting on the command line does not clean up the log immediately, and one of the following trigger conditions needs to be met:

1. The size of binlog exceeds that of max_binlog_size

2. Execute flush logs manually

3. When the MySQL service is restarted

Therefore, if this parameter is set on the command line during the use of MySQL, you can use flush logs to switch logs to trigger cleanup. In this case, you need to note that the cleanup needs to take up the resources of the CVM, and it should be performed without affecting the usage.

Binlog manual cleaning and maintenance

1. Analysis of problems

When the MySQL service has been suspended due to insufficient binlog space, you need to manually clean up the binlog log. The problem that has been dealt with several times is that the administrator deleted all the binlog logs when cleaning the binlog. After the disk space was released, the restart MySQL service found that the startup failed. The reason is that after the binlog is all cleaned, the MySQL service startup needs to write the log to the latest binlog, and the last binlog recorded cannot be found, so it will report an error and failed to start.

1. The correct method of manual cleaning

When manually cleaning the binlog log, you need to keep the latest log to ensure that the latest write log can be detected after the database restart. Confirm the last log, which can be determined according to the log name (serial number increment) or write time.

2. Processing method after deleting all logs

If you have deleted all the logs and cannot start the database, you can do this as follows:

Find the mysql-bin.index file in the datadir directory, which records the binlog information in the database. Clear the contents of the file and restart the database. At this time, the log will be reset to No. 1 and the database will return to normal.

Table emptying operation

1. Analysis of problems

Because some log tables in the database are too large, affecting the efficiency of querying and inserting tables, sometimes emptying tables is done, while administrators are accustomed to using graphical tools such as Navicat when maintaining MySQL databases. When the data of the tables are relatively large, click to empty the tables from the graphical tools, and the system will get stuck, because the situation table in the tool is a delete operation, and each piece of data will be deleted and the redo,undo log will be recorded when the database is executed. The large occupation of database resources leads to the living of database hang.

1. The correct method of emptying the meter

If you confirm that the table data is not needed, use the truncate situation table, which is very efficient and takes up less resources. The SQL syntax is as follows:

Mysql > truncate table_name

MySQL backup

1. Analysis of problems

The most important thing in the database is the data, the security of the data is above all else, and the perfect backup is the last line of defense of data security. Blue Whale platform is an enterprise platform, and the data stored by it is also very important. so the backup strategy must be formulated reasonably. The most commonly used backup methods of MySQL are logical backup mysqldump and physical backup xtrabackup. When the amount of data is small and the backup time can be completed within 1 hour, you can use mysqldump. For a larger amount of data, you need to consider using xtrabackup. The following mainly describes mysqldump.

two。 Backup generic command

Mysqldump is a very flexible tool, and there are many parameters that can be used during backup. However, for mysql backup on BlueKing platform, it is recommended to use the following general backup command:

Mysqldump-uroot-p-- all-databases-- master-data=2-- flush-logs-- single-transaction > / backup/dbfull-date +% F.sql

If you need to compress the backup file, you can use the following command:

Mysqldump-uroot-p-all-databases-master-data=2-flush-logs-single-transaction | gzip > dbfull-date +% F.tar.gz

This command backs up all databases, records the location of the binlog at the time of the backup (used to establish the replication starting point of the master-slave relationship or restore to a specified point in time), backs up in a transactional manner, and does not affect the operation of the master library.

If the backup is on the local disk, you also need to clean the backup files regularly, such as those from 30 days ago. You can refer to the following cleaning script:

Find / backup-mtime + 30-name "dbfull-*.sql"-exec rm-rf {}\

With the wide use of BlueKing platform in the enterprise level, its MySQL database for storing data should ensure the security of data and the smooth operation of MySQL services. Several problems listed in this paper are common problems that lead to the platform not being used, and their processing methods are also verified and used in the generation environment, which can be used as a reference to deal with this kind of problems.

Blue Whale platform MySQL database management standards will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.