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

Let's talk about how to upgrade from mysql5.7 to mariadb10.2.16. The secret of the text is to be close to the topic. So, no gossip, let's just read the following, I believe that after reading the operation of mysql5.7 upgrade to mariadb10.2.16 method of this article you will certainly benefit.

Record an mysql database upgrade operation.

The customer environment cannot be connected to the public network, so the installation of all program applications can only be uploaded to the target CVM by downloading offline software installation packages in advance. The original mysql5.7 database is also installed through the rpm software package.

The database CVM operating system is Red Hat's rhel7.2, 64-bit.

A vulnerability scan of a customer's mysql CVM shows that there is a vulnerability. The database version is as follows:

Mysql > status-mysql Ver 14.14 Distrib 5.7.20, for Linux (x86 / 64) using EditLine wrapperConnection id:15176291Current database:Current user:mysql@localhostSSL:Not in useCurrent pager:stdoutUsing outfile:''Using delimiter:;Server version:5.5.5-10.0.21-MariaDB-log MariaDB ServerProtocol version:10Connection:Localhost via UNIX socketServer characterset:latin1Db characterset:latin1Client characterset:utf8Conn. Characterset:utf8UNIX socket:/opt/beh/core/mysql/sock/mysql.sockUptime:107 days 2 hours 11 min 53 sec

The version is a little strange, using mysql directly-- the version seen by version is mysql server 5.7.2, but the Server version: in status above is 5.5.5-10.0.21-MariaDB-log MariaDB Server. Anyway, you need to upgrade to mariadb version 10.1 or later. Check the latest stable version on the official website, which can be downloaded at:

Https://downloads.mariadb.org/

See that 10.2.16 is a stable version, and the download address for this version is as follows:

Http://mariadb.mirror.iweb.com//mariadb-10.2.16/bintar-linux-systemd-x86_64/mariadb-10.2.16-linux-systemd-x86_64.tar.gz

Upgrade ideas:

Due to the large span of the version, it is not suitable to upgrade one by one, so we use the method of reinstallation, that is, uninstall the original version, and then reinstall the new version.

To ensure data security, select another CVM with the same operating system as the slave, install the same database version, and export the database of the original database CVM through the mysqldump command, and import it into the slave. After the standby is ready, the business application modifies the database connection configuration to connect to the standby database. Then upgrade the original database cloud server.

Operation record:

Database backup:

Back up the original database: mysqldump-u root-p-- all-databases > mysqldump20180712.sql also back up the business database hive separately: mysqldump-u root-p hive > hive20180712.sql

Database standby installation:

The rpm packages installed on the original database CVM are as follows:

[root@hadoop001 dbdata] # rpm-qa | grep mysql

Mysql-community-client-5.7.20-1.el7.x86_64

Mysql-community-common-5.7.20-1.el7.x86_64

Mysql-community-libs-5.7.20-1.el7.x86_64

Mysql-community-devel-5.7.20-1.el7.x86_64

Mysql-community-server-5.7.20-1.el7.x86_64

All the installation rpm packages of the original mysql5.7 are retained in the original database cloud server, and all the rpm installation package files in the original database cloud server are copied to the standby: scp mysql-community-* hadoop002:/data_china/ywb/ installs mysql-related software packages in the standby: yum localinstall mysql-community-* standby starts mysql database: systemctl start mysql

Database standby data import:

Transfer the database backup files from the original database to the standby: scp mysqldump20180712.sql hadoop002:/data_china/ywb/ restore the mysql database on the standby: mysql-u root-p show databases; mysql > use hive; mysql > show tables; mysql > quit

After the verification is successful, the business application is notified to modify the database connection configuration of the program side to make the business application connect to the standby database. After everything is normal, start upgrading the original database cloud server.

Upgrade the original database:

1. Delete the original mysql5.7 version database:

First, Completely delete the mysql5.7 program and database of the original database cloud server: rpm-e mysql-community-server-5.7.20-1.el7.x86_64rpm-e mysql-community-client-5.7.20-1.el7.x86_64rpm-e mysql-community-common-5.7.20-1.el7.x86_64rpm-e mysql-community-libs-5.7.20-1.el7.x86_64rpm-e mysql-community-devel-5.7 .20-1.el7.x86_64 attention When executing the above commands, several of them will fail because of dependencies, so you can adjust the order. Delete the residual file and find the residual file with the following two commands: find /-name mysql whereis mysql

The above two commands will look for mysql-related files and delete all the relevant files that can be deleted. For insurance, you can mv some files that are not sure to be deleted to a specific directory. You can't be wrong to be careful.

After the mysql5.7 is removed, the next step is to follow the new mariadb 10.2.16 version of the database:

2. Install the new version of mariadb 10.2.16 database

Upload the software installation package mariadb-10.2.16-linux-systemd-x86_64.tar.gz prepared above to the original database cloud server hadoop001, and then decompress:

Tar xzvf mariadb-10.2.16-linux-systemd-x86_64.tar.gz

# change the installation directory: be careful not to set up the / usr/local/mysql directory in advance. Just put it in the mv mariadb-10.2.16-linux-systemd-x86_64 / usr/local/mysql/ # go to the / usr/local/mysql directory [root@toydns mysql] # cd / usr/local/mysql# configuration file template under / usr/local/mysql/support-files/. Some of the configured parameters are used in different environments. This is explained here: my-small.cnf is used for small databases or personal testing, and cannot be used in production environment my-medium.cnf, which is suitable for medium-sized databases, such as personal projects or small business projects. My-large.cnf is generally used in cloud servers dedicated to SQL services, that is, hosts dedicated to database services, with higher configuration requirements. Suitable for production environment my-huge.cnf for database services in enterprise cloud servers, generally more for production environment use, so according to the above files, if personal use or testing, then you can use the first two templates Enterprise CVM or highly configured servers above 64G can use the latter two templates, and you can also increase the parameters and expand the configuration to achieve better performance according to your own needs. [root@toydns mysql] # ll support-files/-rw-r--r--. 1 1021 1004 4914 Nov 27 18:32 my-huge.cnf-rw-r--r--. 1 1021 1004 20421 Nov 27 18:32 my-innodb-heavy-4G.cnf-rw-r--r--. 1 1021 1004 4901 Nov 27 18:32 my-large.cnf-rw-r--r--. 1 1021 1004 4914 Nov 27 18:32 my-medium.cnf-rw-r--r--. 1 1021 1004 2840 Nov 27 18:32 my-small.cnf# copy my-medium.cnf to etc and rename it to my.cnf Modify the database file directory cp support-files/my-medium.cnf / etc/my.cnfvi / etc/my.cnf# The MariaDB server [mysqld] port = 3306 socket = / tmp/mysql.sockbasedir = / usr/local/mysql / / mysql directory datadir= / var/lib/mysql / / data storage location innodb_file_per_table = on / / one separate file for each table Easy to manage skip_name_resolve = on / / ignore reverse parsing, speed up access skip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8M:wq pay attention to permissions after saving The directory datadir= / var/lib/mysql needs to be prepared in advance: mkdir / var/lib/mysqlchown mysql:mysql / var/lib/mysql# add MySQL to the PATH environment variable Save the trouble of typing commands in the future [root@toydns mysql] # vi / etc/profile.d/mysql.shexport PATH=/usr/local/mysql/bin:$PATHsource / etc/profile.d/mysql.sh to make this configuration file effective in time # initialize mysql/usr/local/mysql/scripts/mysql_install_db-- user=mysql# create mariadb self-startup script, and add boot cp / usr/local/mysql/support-files/systemd/mariadb.service / usr/lib/systemd/system/ Special note: there is a parameter ProtectSystem=full in the / usr/lib/systemd/system/mariadb.service script, which needs to be changed to false# Prevent writes to / usr, / boot. By default, the ProtectSystem=full,/usr directory is protected from writing data. The datadir directory set above is initially set in / usr/local/mysql/data. When executing systemctl start mariadb, it will report an error and cannot write to the XXX file, because the entire directory of / usr is write-protected. There are two solutions, one is to change ProtectSystem=full to ProtectSystem=false, and the other is to change the location of the datadir directory and not put it in the / usr directory. I am here to change both places. Finally, the service is enabled so that the host can start automatically after restarting. Systemctl enable mariadb starts the mysql database: systemctl start mariadb

Security settings for MySQL

[root@toydns mysql] #. / bin/mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!In order to log into MariaDB to secure it, we'll need the currentpassword for the root user. If you've just installed MariaDB, andyou haven't set the root password yet, the password will be blank,so you should just press enter here.Enter current password for root (enter for none): / / enter directly. Default is empty OK, successfully used password, moving on...Setting the root password ensures that nobody can log into the MariaDBroot user without the proper authorisation.Set root password? [MySQL] y / / whether or not to set the password of MySQL administrator root, y set, enter New password: Re-enter new password: Password updated fulfilling Reloading privilege tables.. twice ... Success!By default, a MariaDB installation has an anonymous user, allowing anyoneto log into MariaDB without having to have a user account created forthem. This is intended only for testing, and to make the installationgo a bit smoother. You should remove them before moving into aproduction environment.Remove anonymous users? [YPop] y / / whether to delete anonymous account y delete... Successful normally, root should only be allowed to connect from 'localhost'. Thisensures that someone cannot guess at the root password from the network.Disallow root login remotely? [YPao] n / / whether root users are not allowed to log in remotely, n cannot be used. Skipping.By default, MariaDB comes with a database named 'test' that anyone canaccess. This is also intended only for testing, and should be removedbefore moving into a production environment.Remove test database and access to it? [Asia] y / / whether to delete the test test database, y delete-Dropping test database... ... Success!-Removing privileges on test database... ... Success!Reloading the privilege tables will ensure that all changes made so farwill take effect immediately.Reload privilege tables now? [YBO] y / / reload the available database table y is. Success!Cleaning up...All done! If you've completed all of the above steps, your MariaDBinstallation should now be secure.Thanks for using MariaDB!

3. Import database backup

After the installation of the new version of database mariadb 10.2.16 is complete, import the database that was backed up:

Mysql-u root-p

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.