Can I copy installed mysql from ubuntu to centos7 directly without installation?

Question

I'm trying to copy an installed MySQL from Ubuntu to Centos7 because I do not want an installation on Centos7 any more. This looks like it works but I'm not sure if there any potential issues which I do not know.

Has anyone tried this operation before?

The purpose to try this copying way is that I'm writing an one click installation sh script which including serveral softwares, such as JDK, MySQL, nginx, elasticsearch and some Java, python code. Coping existing MySQL is obviously the easiest way to finish MySQL installation script, since I need to install MySQL and create accounts and DB's in traditional way .

The steps for my copying MySQL directly from Ubuntu to Centos7 are:

1. create user/group MySQL on Centos7
2. copying MySQL installed directory from ubuntu to the same directory on centos7, such as /usr/local/mysql
3. copying my.cnf file from Ubuntu to /etc on Centos7
4. copying mysql.server to /etc/init.d/mysql
5. grant permission to mysql.server and chown the owner of /usr/local/mysql to mysql:mysql
6. start/stop MySQL service manually, it's looks fine

BTW, my Centos7 machine is new installed, which means I did not installed any other software on it, and my Centos7 can not reach internet so I could not install MySQL through apt, yum ways. I need to download installation package from website firstly, then I can install MySQL through rpm, or tar in the traditional way.

What I want to say is that no matter the apt, yum, rpm, or tar way, it's complicated to write these steps in my one click installation script, so I choose copying directly, but I have some worries about it since I do not know if there are some potential issues.

Answer 1

The simple answer is NO. A more realistic answer would be not easily. If you wanted to do this "the wrong way", you would do something like this:

1. Install the exact same version of MySQL on Ubuntu as you are running on CentOS
2. Copy the entire contents of your /var/lib/mysql directory from CentOS to Ubuntu
3. Modify all of the configuration files to ensure they are using Ubuntu's directory paths rather than those used by CentOS
4. Cross your fingers
5. Start MySQL and hope everything magically works

This is a recipe for disaster as it almost always results in more work than doing it "a better way". Here is how I migrate MySQL data from one server to another:

Note: All of the following commands work on Ubuntu and MySQL.

1. Install MySQL on the target server:

   sudo apt update
   sudo apt install mysql-server
   

2. Configure MySQL:

   sudo mysql_secure_installation
   

   You will be walked through the preliminary setup for MySQL, including the option to set a password for the root account. Do note that even though you've set a password for the root MySQL user, the account is not configured to authenticate with a password when connecting to the MySQL shell. More on this later.

3. Create your MySQL administration account:

   mysql> CREATE USER 'huangxy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'superSecretPassword!123';
   

   If you will be connecting to MySQL remotely, you will need to replace localhost with %.

   Important: Do not use root for all of your administration activities. It is better if you have an account specific to you, even if it has full access to the whole system. root should be used only when absolutely necessary, such as when configuring replication or fixing corrupted databases.

4. Give your account full access:

   mysql> GRANT ALL ON *.* TO 'huangxy'@'localhost' WITH GRANT OPTION;
   

5. Ensure the privileges are written:

   mysql> FLUSH PRIVILEGES;
   

6. Exit MySQL:

   mysql> exit;
   

With this done, now you can create a backup of the MySQL database(s) on your CentOS server. You will need two connections to do this: one to MySQL, and the other to the shell where you can use mysqldump.

1. Lock MySQL, preventing any updates:

   mysql> FLUSH TABLES WITH READ LOCK;
   mysql> SET GLOBAL read_only = ON;
   

   This is done to ensure that nothing interferes with the database export.
2. Dump the databases to disk:

   mysqldump -u root -p --routines --triggers --add-drop-database --all-databases --all-databases > full_backup.sql
   

   If the version of MySQL on CentOS is 8.0.2 or above, you can also migrate your Users along with permissions:

   mysqldump -u root -p mysql user --no-create-info > user_backup.sql
   

   Note: If you are using any version of MySQL lower than 8.0.2, you cannot migrate your users with this command. Instead they will need to be created manually on the new server. This is because the mysql.user table has seen a number of schema changes over the years.
3. If people still need access to the previous system, you can now unlock the databases:

   mysql> SET GLOBAL read_only = OFF;
   mysql> UNLOCK TABLES;
   

   Be sure to advise people that any changes to data in the old system will not be reflected in the new system.
4. Copy the database dumps to the new server
5. Import the databases:

   sudo mysql < full_backup.sql
   

   Note: You do not need to specify -u or enter a password here because it is implied that a person with sudo access on an Ubuntu system has full permissions, therefore another password for MySQL is unnecessary.
6. If applicable, import the user accounts:

   sudo mysql mysql < user_backup.sql
   

   Note: Because the --no-create-info option was used when writing the user data to disk, there is no risk of the table being unnecessarily dropped. If there are any schema differences, an error will be reported and you will need to manually resolve the issues.
7. Connect to MySQL and check that your databases are intact.
8. Restart MySQL just to clear the buffers:

   sudo service mysql restart
   

   So long as there are no issues with the import process, a successful restart will let you know that you're pretty much good to go.

Next Steps ...

Nothing has been said about the various configuration files that MySQL uses for its settings, and that's because it is impossible for me to offer a properly comprehensive outline of what to do. I do not know anything about your server hardware, the databases, or how the systems are used. What I can recommend is this:

1. Tweak the basic InnoDB settings for your hardware
2. Monitor the system and make incremental changes as you see fit

I would not recommend copy/pasting the .cnf files from CentOS to Ubuntu, as this would be suboptimal.

While this answer is rather long and may look complicated, it's generally the most reliable way in my experience to move from one server and/or OS to another.