Considerations for MySQL database migration

I used MySQL on CentOS in the past. Later, because of the test requirements, I needed to migrate the website and data stored in the database to the new system for testing, the target system is RedHatEnterpriseLinuxServer (RHEL ). Since the database has never been migrated before, it immediately encountered a problem. Other issues are not listed. The permissions are only displayed in the following commands and execution results. Note: Some displays are blocked. Sun25Mar

I used MySQL on CentOS in the past. Later, because of the test requirements, I needed to migrate the website and data stored in the database to the new system for testing, the target system is RedHat Enterprise Linux Server (RHEL ).

Since the database has never been migrated before, it immediately encountered a problem.

Other issues are not listed. The permissions are only displayed in the following commands and execution results.

Note: Some displays are blocked.

 
 

  
  
Sun 25 Mar 2012 05:07:30 CST
  
  
Error/usr/bin/mysqlshow: Cannot list tables in ********: Can't read dir '. /********/'(errno: 13)
  
  
Google keywords: mysql errno: 13
  
  
Solution: the sub-database of the MySQL database must be owned by the mysql user and the mysql group. Otherwise, mysql cannot read the migrated database.
  
  
[Root @ localhost mysql] # ls-al
  
  
Total

  
  
Drwxr-xr-x. 7 root 4096 Mar 24.
  
  
Drwxr-xr-x. 48 root 4096 Mar 24 ..
  
  
-Rw-r -----. 1 root 27262976 Mar 24 ibdata1
  
  
-Rw-r -----. 1 root 5242880 Mar 24 ib_logfile0
  
  
-Rw-r -----. 1 root 5242880 Mar 24 ib_logfile1
  
  
Drwx ------. 2 root 4096 Mar 24 ********
  
  
Drwx ------. 2 root 4096 Mar 24 mysql
  
  
Drwx ------. 2 root 4096 Mar 24 performance_schema
  
  
Drwx ------. 2 root 4096 Mar 24 ********

  
  
Drwx ------. 2 root 4096 Mar 24 ********

  
  
[Root @ localhost mysql] # ls/var/lib/mysql/-al
  
  
Total

  
  
Drwxr-xr-x. 5 mysql 4096 Mar 25.
  
  
Drwxr-xr-x. 54 root 4096 Mar 24 ..
  
  
-Rw ----. 1 mysql 10485760 Mar 25 ibdata1
  
  
-Rw ----. 1 mysql 5242880 Mar 25 ib_logfile0
  
  
-Rw ----. 1 mysql 5242880 Mar 25 ib_logfile1
  
  
Drwx ------. 2 root 4096 Mar 25 ********

  
  
Drwx ------. 2 mysql 4096 Mar 25 mysql
  
  
Srwxrwxrwx. 1 mysql 0 Mar 25 16:23 mysql. sock
  
  
Drwx ------. 2 root 4096 Mar 25 ********

  
  
[Root @ localhost mysql] # chown-R mysql: mysql ./********/

  
  
[Root @ localhost mysql] # chown-R mysql: mysql ./********/
  
  
[Root @ localhost mysql] # service mysqld restart
  
  
Stopping mysqld: [OK]
  
  
Starting mysqld: [OK]
  
  
[Root @ localhost mysql] # ls-al
  
  
Total

  
  
Drwxr-xr-x. 7 root 4096 Mar 24.
  
  
Drwxr-xr-x. 48 root 4096 Mar 24 ..
  
  
-Rw-r -----. 1 root 27262976 Mar 24 ibdata1
  
  
-Rw-r -----. 1 root 5242880 Mar 24 ib_logfile0
  
  
-Rw-r -----. 1 root 5242880 Mar 24 ib_logfile1
  
  
Drwx ------. 2 mysql 4096 Mar 24 ********
  
  
Drwx ------. 2 root 4096 Mar 24 mysql
  
  
Drwx ------. 2 root 4096 Mar 24 performance_schema
  
  
Drwx ------. 2 root 4096 Mar 24 ********
  
  
Drwx ------. 2 mysql 4096 Mar 24 ********

  
  
[Root @ localhost mysql] # cd/var/lib/mysql/
  
  
[Root @ localhost mysql] # ls-al
  
  
Total

  
  
Drwxr-xr-x. 5 mysql 4096 Mar 25.
  
  
Drwxr-xr-x. 54 root 4096 Mar 24 ..
  
  
-Rw ----. 1 mysql 10485760 Mar 25 ibdata1
  
  
-Rw ----. 1 mysql 5242880 Mar 25 ib_logfile0
  
  
-Rw ----. 1 mysql 5242880 Mar 25 ib_logfile1
  
  
Drwx ------. 2 root 4096 Mar 25 ********
  
  
Drwx ------. 2 mysql 4096 Mar 25 mysql
  
  
Srwxrwxrwx. 1 mysql 0 Mar 25 17:12 mysql. sock
  
  
Drwx ------. 2 root 4096 Mar 25 ********

  
  
[Root @ localhost mysql] # chown-R mysql: mysql ./********/
  
  
[Root @ localhost mysql] # chown-R mysql: mysql ./********/
  
  
[Root @ localhost mysql] # ls-al
  
  
Total

  
  
Drwxr-xr-x. 5 mysql 4096 Mar 25.
  
  
Drwxr-xr-x. 54 root 4096 Mar 24 ..
  
  
-Rw ----. 1 mysql 10485760 Mar 25 ibdata1
  
  
-Rw ----. 1 mysql 5242880 Mar 25 ib_logfile0
  
  
-Rw ----. 1 mysql 5242880 Mar 25 ib_logfile1
  
  
Drwx ------. 2 mysql 4096 Mar 25 ********
  
  
Drwx ------. 2 mysql 4096 Mar 25 mysql
  
  
Srwxrwxrwx. 1 mysql 0 Mar 25 17:12 mysql. sock
  
  
Drwx ------. 2 mysql 4096 Mar 25 ********

  
  
[Root @ localhost mysql] # service mysqld restart
  
  
Stopping mysqld: [OK]
  
  
Starting mysqld: [OK]
  
  
[Root @ localhost mysql] #
 
 

To put it bluntly, you need to pay attention to permission assignment. Because MySQL databases must be created and owned by mysql. Because in general, only the root permission is obtained after migration-664. Therefore, only the chown command is used.

Appendix:

Chown usage:

Chown-R user name: target file or directory of the user group

R indicates Recursive Execution.