Cara menggunakan upgrade mysql linux

Early adopters of our customers are considering to upgrade to MySQL 8.0. To be prepared we have created a check-list:

Table of Contents

• To upgrade MySQL 5.6 to 5.7 on CentOS 7
• To upgrade MySQL 5.7 to 8.0 on Ubuntu/Debian
• To upgrade MariaDB 5.5 to 10.1 on CentOS 7
• To upgrade MariaDB 10.1 to 10.5 on Ubuntu/Debian
• Related Tutorials

• RTFM: Upgrading MySQL. Really! This time is different than all the years before...
• Upgrade Path:
  • Only Upgrade from 5.7 to 8.0 is supported [skipping 5.7 is NOT supported!]. So Upgrade to 5.7 first!
  • Only Upgrade between GA releases [5.7.9+, 8.0.11+] is supported.
  • Recommended: Upgrade to newest 5.7 first [5.7.25].
• Check for Incompatibility Changes, Deprecated and Removed Features:
  • Incompatibility Changes in MySQL 8.0
  • Removals:
    • Query Cache was removed.
    • No support for non-native partitioning [MyISAM, Archive, etc.]
    • Reserved keywords [application changes]
    • No own tables in MySQL schema
    • No Foreign Key Constraint names longer than 64 characters
    • No obsolete sql_mode definitions [requires application changes]
    • Check ENUM and SET length [< 255 characters/1020 bytes]
    • No tables in System Tablespace [ibdata1] or General Tablespaces!
    • GROUP BY with ASC or DESC is deprecated [application change]
    • log_errors/log_warnings
    • PASSWORD[]
  • Reserved Keywords
  • Server Variables
  • Default Changes
    • Character Set [utf8mb4]
    • Default authentication plugin → Application impact.
    • log_bin = ON
  • Some database [admin] tools and applications might not work any more after upgrade!
• Upgrade MySQL language Interfaces as well!
  PHP: mysqli/mysqlnd, Perl: DBD::mysql, Python: MySQLdb, Java: Connector/J,...
• Test Upgrade AND Application first on a testing system created with a Physical Backup!
• Read Release Notes: New policy: Changes WITHIN Major Release Series are possible [= New Features]!
• Prepare Upgrade
  • Check for Upgrade

    shell> mysqlcheck --user=root --all-databases --check-upgrade

  • MySQL Upgrade from MySQL 8.0 Shell is recommended!
  • Install MySQL Shell 8.0

    shell> ./mysqlsh root:@localhost:3306
    mysql-js> util.help["checkForServerUpgrade"];
    mysql-js> util.checkForServerUpgrade[]

    or

    shell> ./mysqlsh root:@localhost:3306 -e "util.checkForServerUpgrade[];"

• Backup before Upgrade preparation!
• Fix findings from MySQL Upgrade Check first!
• Backup!
  Downgrade is NOT supported!!!
• Upgrade types:
  • physical [inplace] Upgrade
  • logical Upgrade
• Physical [inplace] Upgrade

  • mysql> XA RECOVER;

  • Rotate Keyring Master Key:

    mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;

  • mysql> SET GLOBAL innodb_fast_shutdown = 0;

  • Shutdown old MySQL 5.7 Server.
  • Exchange binaries.
  • Adjust MySQL Configuration [my.cnf].
  • Start new MySQL 8.0 Server
• Logical [dump/restore] Upgrade

  • mysql> mysqldump --user=root --all-databases --events > full_dump.sql

  • Shutdown old MySQL 5.7 Server.
  • Exchange binaries.
  • Remove old Instance [shell> rm -rf /var/lib/mysql/*]
  • Adjust MySQL Configuration [my.cnf].
  • Create a new, empty MySQL 8.0 Instance:

  • mysql> mysqld --initialize-insecure --datadir=/var/lib/mysql

  • Start new MySQL 8.0 Server
  • Restore dump: shell> mysql --user=root --force < full_dump.sql [Might take some very long time]

    ERROR 3554 [HY000] at line 6400: Access to system table 'mysql.innodb_index_stats' is rejected.
    ERROR 1062 [23000] at line 6402: Duplicate entry 'Journal-Events-PRIMARY-n_diff_pfx01' for key 'PRIMARY'
    ERROR 3554 [HY000] at line 6410: Access to system table 'mysql.innodb_table_stats' is rejected.
    ERROR 1062 [23000] at line 6430: Duplicate entry 'Journal-Events' for key 'PRIMARY'

• Post Upgrade work
  • shell> mysql_upgrade --user=root [might take some very long time].
  • shell> mysql --user=root mysql < share/fill_help_tables.sql [seems to not exist any more in 8.0.15... Bug?]
  • Restart Server
  • Check Error Log
  • Do a backup again.

Literature

• INPLACE upgrade from MySQL 5.7 to MySQL 8.0
• Upgrading to MySQL 8.0? Here is what you need to know…
• How to safely upgrade to MySQL 8.0?

MySQL & MariaDB are open-source relational database management systems and are possible to use in most Linux distributions. It uses SQL [Structured Query Language] to manage its data.

This guide outlines the basic steps required to upgrade MySQL and MariaDB on Linux systems.

To upgrade MySQL 5.6 to 5.7 on CentOS 7

1. Check the current version of MySQL on the server.

   # mysql --version
   

2. Before initiating the upgrade, take the full backup of all databases that exist on the server with the below command.

   # mysqldump -u root -p --all-databases > /tmp/all-database.sql
   

   
   Enter the root password and then the all-database.sql will be created under /tmp directory:

3. Stop MySQL service with the below command.

   # systemctl stop mysqld
   

4. Now create the backup of the current MySQL data with the below command.

   # cp -r /var/lib/mysql /var/lib/mysql.backup
   

5. Download the MySQL RPM which needs to upgrade. Here downloading the MySQL 5.7 RPM to /tmp directory with the below command.

   # wget //repo.mysql.com/mysql57-community-release-el7.rpm -P /tmp/
   

6. List the /tmp directory on the server to verify whether it downloaded.

   # cd /tmp/
   
   # ll | grep mysql
   

7. Now need to remove the MySQL-Community RPM that contains the current version of MySQL 5.6.

   # yum remove mysql-community-release
   

8. Install the recently downloaded MySQL 5.7 RPM/

   # rpm -ivh /tmp/mysql57-community-release-el7.rpm
   

9. Update the MySQL repository to the recently installed 5.7.

   # yum update mysql
   

10. Now restart the MySQL service and check the version with the below commands.

    # systemctl restart mysqld
    
    # mysql --version
    

11. Here from the last image, you can verify that the MySQL service upgraded from 5.6 to 5.7.

To upgrade MySQL 5.7 to 8.0 on Ubuntu/Debian

1. Check the current version of the MySQL on the server

   # mysql --version
   

2. Before initiating the upgrade, take the full backup of all databases that exist on the server with the below command.

   # mysqldump -u root -p --all-databases > /tmp/all-database.sql
   

   Enter the root password and then the all-database.sql will be created under /tmp directory:

3. Stop MySQL service with the below command.

   # systemctl stop mysql.service
   

4. Now create the backup of the current MySQL data with the below command.

   # cp -r /var/lib/mysql /var/lib/mysql.backup
   

5. Now download the MySQL APT repository.

   # wget //repo.mysql.com//mysql-apt-config_0.8.14-1_all.deb
   

6. Then add the MySQL APT repository to the server’s software repository list with the below command.

   # dpkg -i mysql-apt-config_0.8.14-1_all.deb
   

   The below screens will get and select the MySQL Server & Cluster option and press enter.

   
   Now select the mysql-8.0 and press OK.

   Click on the below-mentioned OK to finish.

   Then will get the below message.

7. Now update the server and install the latest MySQL using the below commands.

   # apt-get update
   
   # apt-get install mysql-server
   

8. Now restart the MySQL service and check the version with the below commands.

   # systemctl restart mysql.service
   
   # mysql --version
   

9. Here from the last image, you can verify that the MySQL service upgraded from 5.7 to 8.0.

To upgrade MariaDB 5.5 to 10.1 on CentOS 7

1. Check the current version of the MariaDB on the server and Enter the MariaDB root password.

   # mysqladmin -u root -p version
   

2. Before initiating the upgrade, take the full backup of all databases that exist on the server with the below command.

   # mysqldump -u root -p --all-databases > /tmp/all-database.sql
   

   
   Enter the MariaDB root password and then the all-database.sql will be created under /tmp directory.

3. Stop MariaDB service with the below command.

   # systemctl stop mariadb
   

4. Now create the backup of the current MariaDB data with the below command.

   # cp -r /var/lib/mysql /var/lib/mysql.backup
   

5. Now need to add the latest MariaDB 10.1 repo for CentOS 7 distributions. For that add the following text in /etc/yum.repos.d/MariaDB10.repo with the text editor vi.

   # MariaDB 10.1 CentOS repository list - created 2016-01-18 09:58 UTC
   
   # //mariadb.org/mariadb/repositories/
   
   [mariadb]
   name = MariaDB
   baseurl = //yum.mariadb.org/10.1/centos7-amd64
   gpgkey=//yum.mariadb.org/RPM-GPG-KEY-MariaDB
   gpgcheck=1
   

6. Now remove the current MariaDB 5.5 from the server with the below command.

   # yum remove mariadb-server mariadb mariadb-libs
   

7. Then remove the repository cache with the below command.

   # yum clean all
   

8. Now proceed with the Installation MariaDB 10.1.

   # yum install MariaDB-server MariaDB-client
   

9. Once the installation is completed, start and enable MariaDB.

   # systemctl start mariadb
   
   # systemctl enable mariadb
   

10. Now run the below command to check the new version of MariaDB.

    # mysqladmin -u root -p version
    

    
    Enter the MariaDB root password.

11. Here from the last image, you can verify that the MariaDB server upgraded from 5.5 to 10.1.

To upgrade MariaDB 10.1 to 10.5 on Ubuntu/Debian

1. Run the below command to check the current version of the MariaDB on the server and Enter the MariaDB root password.

   # mysqladmin -u root -p version
   

2. Before initiating the upgrade, take the full backup of all databases that exist on the server with the below command.

   # mysqldump -u root -p --all-databases > /tmp/all-database.sql
   

   Enter the MariaDB root password and then the all-database.sql will be created under /tmp directory:

3. Stop MariaDB service with the below command.

   # systemctl stop mariadb
   

4. Now create the backup of the current MariaDB data with the below command.

   # cp -r /var/lib/mysql /var/lib/mysql.backup
   

5. Now run the below commands to add the MariaDB PPA on the server.

   # apt-get install software-properties-common
   

   # apt-key adv --fetch-keys '//mariadb.org/mariadb_release_signing_key.asc'
   

   # add-apt-repository 'deb [arch=amd64,arm64,ppc64el] //mirrors.supportex.net/mariadb/repo/10.5/ubuntu bionic main'
   

   NOTE: Replace ubuntu bionic with your distribution name and release.

6. Now remove the current MariaDB 10.1 from the server with the below command.

   # apt remove mariadb-server
   

7. Then remove the repository cache with the below command.

   # apt-get clean all
   

8. Now proceed with the Installation MariaDB 10.5

   # apt-get install mariadb-server
   

9. Once the installation is completed, start and enable MariaDB.

   # systemctl start mariadb
   
   # systemctl enable mariadb
   

10. Now run the below command to check the new version of MariaDB.

    # mysqladmin -u root -p version
    

    
    Enter the MariaDB root password.

11. Here from the last image, you can verify that the MariaDB server upgraded from 10.1 to 10.5.

Related Tutorials

• Installing MySQL on Linux Cloud Servers

• Installing MariaDB on Linux Cloud Servers

• Import and Export Databases in MySQL and MariaDB on Linux Cloud Servers

• Resetting the MySQL and MariaDB Root Password

• How to Backup and Restore MySQL or MariaDB on Linux Cloud Servers

• How to reset WordPress Admin Password via MySQL Command Line