How to change MySQL version
| Category path: | ISPmanager Business -> Database servers |
| ISPmanager Lite -> Database servers |
Attention! We do not recommend that you change the main MySQL version. This may prevent the control panel from working correctly. We do not provide technical support if the incorrect operation of ISPmanager is caused by the change of the main MySQL version.
The steps described below should be done unless you know exactly what you are doing and have enough server administration skills.
Contents
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.
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
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.
