Qfyilyi

i am facing issue regarding mysql.user's table in mysql 5.6.36 server , previously my slave used to be on 5.6.22 version and we have taken a dump and migrated to new server , but due to version incompatibility we are unable to create new users , it is giving error to upgrade , is there anyway so that i can just upgrade only mysql.user table instead of running mysql_upgrade on entire slave databases.

ERROR :

Column count of mysql.user is wrong. Expected 43, found 42. Created with MySQL 50622, now running 50636. Please use mysql_upgrade to fix this error.

You need to run mysql-upgrade --upgrade-system-tables (See Documentation)

This will bypass checking all other tables and focus just on the mysql schema.

I was able to upgrade only one table of my database this way : It worked for me. My scenario. MySQL 5.1 to 5.6 only I run into a problem upgrading one of my databases table (the biggest one), did not dump correctly and was rejected during upload to new server, took forever and timed out several times, so I had to do something out of the books. (Before you do download your database, do a complete backup, repair, check and all the maintenance required so you dont have messed up data in your new upload. Also, do a direct wire transfer, dont use wireless with large databases.

1 - All my tables, except the biggest one, uploaded without a problem (regular export then regular import thru phpmyadmin from 5.1 to 5.6 MySQL different servers side by side with my laptop in the middle)

2 - I went into the MySQL source data directory (depends on your system but on Centos 7 is in /var/lib/mysql/) and search the table with the problem, it has 3 files named after the table (table_name.MYD, table_name.MYI and table_name.FRM) you need to download those 3 manually thru FTP like Filezilla or copy into a USB)

3 - Just in case, create a target blank temp_database and give all the rights to a user. MySQL creates an additional directory called temp_database where you are to copy those 3 files for that table. (dont need to mess with good upgraded tables, if you need to do upload all the tables, you can proceed to copy all the tables files into that new temp directory, is faster when you do copy directly without using MySQL)

4 - Once you transfer the table files into that new target temp directory, then you will be able to see them on phpmyadmin (on the new server) as temp_database, check the table, make sure all data and records are there correctly, displayed and accounted for.

5 - Since we created a copy thru FTP or a USB plain copy, then obviously the file is READ ONLY, and performing a check or repair table it will tell you so. You need to do CHMOD thru shell on those files so MySQL can process the repair. I guess, while repairing the file, it does the repair and upgrade of table format or any other version problems.

6 - After you are happy with the table, and you can insert, update and delete one record as test, then you can just move that table or tables onto the right database using phpmyadmin to make everything work as usual. Remove that temp database and you are set.

I have only performed that in that scenario, 5.1 to 5.6 upgrade. I don't know if same procedure will work for other versions, havent tryied, but anyone reading this article, can follow my procedure and update us if it does work with other versions of MySQL or other databases such as MariaDB, let us know.

Hope it helps. Cheers.