Solve some errors caused by mysql version upgrade

Some errors occurred during the project due to the database version.

After the data is restored, the table structure cannot be displayed.

After the mysql server is upgraded and the database data is restored, the structure of the desc table using navicat appears.

"1558-Column count of mysql. proc is wrong. Expected 20, found 16. Created with MySQL 50018, now running 50146. Please use mysql_upgrade to fix this error."

My mysql server is upgraded from mysql 5.1 to mysql 5.5, and the backup and recovery of the original database table are directly copied to the mysql data directory; the data is intact. Search to find out

The solution is:

Mysql. proc
This is from the system table in 5.1, which is used to record the stored procedure or function information. Your database must have been upgraded or backed up for migration and recovery using different versions.

Use the command mysql_upgrade to solve the problem.

Mysql_upgrade-u root-datadir =/opt/mysql-basedir =/opt/mysql/data-password = 123456

MySQL utility for upgrading databases to new MySQL versions.

5.6.10 mysqldump: Couldn't execute 'Select @ GTID_MODE 'execution error

Mysqldump: Couldn't execute 'Select @ GTID_MODE ': Unknown system variable 'gtid _ mode' (1193)

I don't know the system variable GTID_MODE. google search found a bug report in mysql.

Mysqldump-set-gtid-purged = AUTO does not detect if mysqld has GTIDs

5.6.5 introduced GTIDs. mysqldump 5.6 was made
Compatible to use GTIDs
2. In versions <5.6, where GTIDs are not
Even defined, mysqldump 5.6 fails as it cannot
Execute 'Select @ GTID_MODE'
3. using-set-gtid-purged = AUTO, shocould detect whether
The server has GTIDs enabled or not. However, this
Option works only for 5.6 versions.

WORKAROUND: if you are using mysqldump 5.6 to backup mysql-5.5 and old database
Versions, use-set-gtid-purged = OFF option.

Fix: THe fix is to check for the server version before executing 'Select @ gtid_mode'

5.6.5 introduces GTIDs. If mysql version is less than 5.6, GTIDs is not defined. mysqldump5.6 cannot run 'Select @ GTID_MODE '; -set-gtid-purged = AUTO should be used to check whether GTIDs is enabled, but this option can only work on mysql 5.6.

The remote server I want to back up is

An error is reported if the following mysqldump syntax is used.

Therefore, when using mysqldump 5.6 to back up data of mysql server versions earlier than mysql server 5.6 or earlier, the statement "SELECT @ GTID_MODE" will be executed first (although the attribute value obtained by select @ gtid_mode is off)

The-set-gtid-purged = OFF attribute must be added. The export is successful.

 

However, I don't quite understand what this gtid_mode attribute means. For more information about Transaction data synchronization, see 16.1.4.5. Global Transaction ID Options and Variables.

"This is because when GTID mode is enabled, Transaction consistency cannot be guaranteed when non-transaction tables are updated. Therefore, set disable-gtid-unsafe-statements = 1 to ensure master-slave data consistency ."

[MySQL FAQ] series-when GTID and binlog are enabled, the newly installed MySQL prompts that the logon fails.

[Root @ imysql mysql] # mysql
ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: NO)
A: The database has been initialized by mysql_install_db. It should be okay. After investigation, we found that the permission mode of the $ datadir mysql database directory is:

Drwx ------ 2 mysql root 4096 Jan 9 mysql
It seems that there is no problem. Check the error log and find a row:

130109 21:04:23 [Warning] Bootstrap mode disables GTIDs. Bootstrap mode shoshould only be used by mysql_install_db which initializes the MySQL data directory and creates system tables.
ERROR: 1785 Updates to non-transactional tables are forbidden when DISABLE_GTID_UNSAFE_STATEMENTS = 1.
130109 21:04:23 [ERROR] Aborting
In addition, the user tablespace file in the mysql database is displayed as follows:

-Rw ---- 1 mysql 10684 Jan 9 user. frm
-Rw ---- 1 mysql 0 Jan 9 20:22 user. MYD
-Rw ---- 1 mysql 1024 Jan 9 user. MYI
The size of the user tablespace data file is 0.
It seems that it has something to do with enabling GTID mode. After careful check, we found that GTID-related options are:

Gtid_mode = ON
Binlog_format = mixed
Disable-gtid-unsafe-statements = 1
Log-bin = binlog
This is because when GTID mode is enabled, Transaction consistency cannot be guaranteed when non-transaction tables are updated. Therefore, set disable-gtid-unsafe-statements = 1 to ensure master-slave data consistency.

Solution:
In this case, you can change the value of disable-gtid-unsafe-statements to 0. You can also disable the GTID and binlog options at the same time and enable them after initialization.

# Gtid_mode = ON
# Log-bin = binlog
Or

Disable-gtid-unsafe-statements = 0