How to change MySQL version

From ISPWiki
Jump to: navigation, search

The steps described below should be done unless you know exactly what you are doing and have enough server administration skills.

Installation on a clean server

You can change the default MySQL version that is set up during ISPmanager 5 lite installation (connect a certain repository to a freshly installed operating system). You can find rpm and deb packages at Repo MySQL. For more information on how to install the repository, please refer to Repo MariaDB.

Let's describe an example of installing MySQL 5.7 repository on CentOS 7, and MariaDB 10.1 on Debian 8

Installing MySQL 5.7 repository on CentOS 7

Download and install the rpm file.

cd /root/
wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
rpm -i /root/mysql57-community-release-el7-9.noarch.rpm
yum makecache

After installation, the /etc/yum.repos.d/mysql-community.repo. file will be added to your server:

# Enable to use MySQL 5.5
[mysql55-community]
name=MySQL 5.5 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.5-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.6
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

If you need to install another MySQL version, you can choose it in the file (MySQL 5.5 - 5.7). Specify the enabled=1 parameter for a required MySQL version, and enabled=0 for other versions.

Installing MariaDB 10.1 repository on Debian 8

Connect the MariaDB repository

apt-get install software-properties-common
apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
add-apt-repository 'deb [arch=amd64,i386] http://lon1.mirrors.digitalocean.com/mariadb/repo/10.1/debian jessie main'
apt-get update

Alternatively, you can add the following lines to /etc/apt/sources.list file:

# http://downloads.mariadb.org/mariadb/repositories/
deb [arch=amd64,i386] http://lon1.mirrors.digitalocean.com/mariadb/repo/10.1/debian jessie main
deb-src http://lon1.mirrors.digitalocean.com/mariadb/repo/10.1/debian jessie main

And execute

apt-get update

Next, install the MySQL server. Centos 6/7

yum install mysql

Debian/Ubuntu

apt-get install mariadb-server

Install ISPmanager according to the ISPmanager installation guide

The following script will automatically add a database server to ISPmanager on Centos 7 and MySQL 5.7

#/bin/bash
systemctl start mysqld.service
#find a temporary password 
OLDPASS=`grep 'temporary password' /var/log/mysqld.log | awk '{ print $NF }'`
#generate a password
NEWPASS=`</dev/urandom tr -dc '125.:?+fuHjmNGh' | head -c15; echo ""`
#chnage the password 
mysqladmin -u root -p$OLDPASS password $NEWPASS
#Add a database server in the panel
/usr/local/mgr5/sbin/mgrctl -m ispmgr db.server.edit host=localhost name=MySQL5.7 username=root password=$NEWPASS sok=ok type=mysql
#disable passwords policy 
mysql -u root -p$NEWPASS << EOF
uninstall plugin validate_password;
EOF

How to change MySQL version on ISPmanager server

Before changing MySQL version, please, make a backup copy of all databases. You should delete MySQL server from ISPmanager in the "Features" module, therefore you will delete the ispmanager-pkg-mysql package. Then connect a repository with a required version #MySQL 5.7 repository installation on CentOS 7#MariaDB 10.1 repository installation on Debian 8 If you need to upgrade the version (for example, MySQL 5.5 to MySQL 5.7, MariaDB 10.0 to MariaDB 10.2), execute the command:

Centos 6/7

yum install mysql

Debian/Ubuntu

apt-get install mariadb-server

Then run the command:

mysql_upgrade

If you сhange MySQL to a lower version or change it to MariaDB. Delete the existing MySQL server. Pay your attention to all associated packages.


Centos 6/7

yum remove mysql

Debian/Ubuntu

apt-get remove mysql

If the related packages are also deleted (except coremanager-mysql), you can delete a package using the package manager. (you can find their names in the package manager as well). How to find a package name:


Centos 6/7

rpm -qa | grep mariadb

Debian/Ubuntu

dpkg -l | grep mysql

Delete the packages without dependencies:


Centos 6/7

rpm -e --nodeps mysql-server

Debian/Ubuntu

dpkg --remove --force-depends mariadb-server

Delete all the packages associated with the current MySQL server. Install the MySQL server:


Centos 6/7

yum install mysql

Debian/Ubuntu

apt-get install mariadb-server

Then run the command:

mysql_upgrade

Update the path to mysqld. Execute the following command to get the actual path to the executable file:

[user@domain ~]# which mysqld
/usr/sbin/mysqld

Add the path into the /usr/local/mgr5/etc/ispmgr.conf.d/mysql.conf file:

path mysqld /usr/sbin/mysqld

Install the MySQL server in ISPmanager --> "Features". Next, navigate to "Services" and check that the MySQL server is up and running, otherwise run the MySQL server manually. Then, check "Database servers". If there is no database server, you should add it. You may need to reset the password of MySQL root user. Enservermysql.png

How to reset root MySQL password

Add the skip-grant-tables option into the /etc/my.cnf file Run the MySQL server

Centos 6/Debian/Ubuntu:

/etc/init.d/mysql restart

Centos 7:

systemctl restart mariadb

Or

systemctl restart mysqld

Change the root password

mysql
use mysql;
UPDATE user SET Password=PASSWORD("new_password") WHERE User='root';
FLUSH PRIVILEGES;

MySQL 5.7:

mysql
use mysql;
UPDATE user SET authentication_string=PASSWORD("new_password") WHERE User='root';
FLUSH PRIVILEGES;

Delete the skip-grant-tables option from /etc/my.cnf, and reboot MySQL.

Centos 6/Debian/Ubuntu:

/etc/init.d/mysql restart

Centos 7:

systemctl restart mariadb

Or

systemctl restart mysqld

Create a new database server in ISPmanager using the new MySQL password. If the server already exists, and click "Ok" in order to check that connection works correctly. Go to the "Databases" module to check that the DB is working and you can manage it If databases were deleted, you should restore them from backup. Please note: different MySQL versions has different system tables structures. For example, in MySQL 5.7 the Password field is changed into authentication_strings that's why, before recovery, you should bring into compliance fields of the system tables in the DB dump, or do not recover them.

Possible issues

The ""server name" doesn't exist" error can occur in ISPmanager EnErrorservername.png

In the panel's log file you can find the following error:

ERROR Failed to load database driver library 'libmysql.so.5.83.0'. Error: libmysqlclient.so.18: cannot open shared object file: No such file or directory

It means that the MySQL library package was lost during installation/reinstallation. You should install it manually, for example on CentOS 7 and MySQL 5.7 the name of the package is mysql-community-libs-compat-5.7.16-1.el7.x86_64.