Several years ago I built a server to run all of the MySQL databases I use for various web servers and applications. Now, it is way out of date and I need to update it. The official MySQL guide says that I should update from 5.0 to 5.1 to 5.2 to 5.3 to 5.4 to 5.5 to 5.6 to 5.7. I’m going to upgrade directly from 5.0 to 5.7, and document it here.
First, I setup a a centos 6.7 server, in a basic configuration. Surprisingly, when I groupinstalled the mysql server, it installed MySQL v5.1 (!), which is the default for Centos 6.7. Since I want to install v5.7, I needed to a couple more steps… 1.) Uninstall MySQL 5.1, 2.) Setup the yum repository for MySQL 5.7, and 3.) Install MySQL 5.7
1. Uninstall MySQL 5.1
yum groupremove 'MySQL Database server'
2. Setup the repository for MySQL to point to version 5.7:
cd /usr/local/src wget http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm yum localinstall mysql57-community-release-el6-7.noarch.rpm
For more detailed instructions on how to setup the MySQL repository for different versions and different operating systems, please look here:
How to Install Latest MySQL 5.7.9 on RHEL/CentOS 7/6/5 and Fedora 23/22/21
3. Install the MySQL server from the newly specified repository (in Step 1.)
yum groupinstall 'MySQL Database server'
A quick test to verify the version, if we are at MySQL v 5.7, then we’re ready to start the migration.
# mysql --version mysql Ver 14.14 Distrib 5.7.11, for Linux (x86_64) using EditLine wrapper
We’re ready to start the conversion!
Our approach to the conversion is to export the users and permissions, and then run that sql on the new server. Once the users and permissions , and then generate dump files for each of the schemas. Then we will re-create the schemas in the new server using the dump files.
Let us generate the code that will create the users and permissions in the new database server. This is a two step process.
1.) Generate a file containing the commands to show the permissions for each user.
2. ) Run the command file created in step 1.) to generate a list of user and permissions on the system.
1.) Log into the old database server, log into mysql via your favorite way, and run this command:
SELECT DISTINCT
CONCAT('SHOW GRANTS FOR \'',
user,
'\'@\'',
host,
'\';') AS query
FROM
mysql.user
;
You should get a list of users, but wrapped with the text specified… something like this:
GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*85CE1D13534' GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
Once that is working, we’ll send the output of this to a file… called ‘myfile’
# mysql -uroot -N -p -s > myfile
Enter password:
SELECT DISTINCT
CONCAT('SHOW GRANTS FOR \'',
user,
'\'@\'',
host,
'\';') AS query
FROM
mysql.user
;
quit
#
Look at the contents of ‘myfile’ to verify all is well…
# more myfile SHOW GRANTS FOR 'user1'@'%'; SHOW GRANTS FOR 'user2'@'%'; ... SHOW GRANTS FOR 'usern'@'%';
All looks good. We just generated a file that contains the commands to ‘dump’ the users and permissions.
2.) Execute the command in ‘myfile’ and sent the output to a file called ‘grantfile’
]# mysql -uroot -N -p -s -r < myfile > grantifle
Enter password:
[root@dbaselocal migrate]#
Look at the contents of ‘grantfile’ to verify:
# more grantfile GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' IDENTIFIED BY PASSWORD '*111112' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'user3'@'%' IDENTIFIED BY PASSWORD '*abc' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'user3'@'%' IDENTIFIED BY PASSWORD '*xxx' WITH GRANT OPTION; GRANT USAGE ON *.* TO 'user4'@'%' IDENTIFIED BY PASSWORD '*yyy'; GRANT ALL PRIVILEGES ON `user5`.* TO 'db1'@'%'; GRANT ALL PRIVILEGES ON `user6`.* TO 'db2'@'%'; ... GRANT USAGE ON *.* TO 'usern-1'@'%' IDENTIFIED BY PASSWORD '*bbb'; GRANT SELECT, SHOW VIEW ON `usern`.* TO 'db4'@'%';
This is the file that will create the users and database permissions on the new server.
Please note that I needed to put a semicolon at the end of each line… ‘;’
We’ll need to copy this file to the new server, and then execute it.
Log onto the new server and ftp to the old server, get the grantfile
sftp 192.168.0.23 Connecting to 192.168.0.23... dan@192.168.0.23's password: sftp> cd /home/dan/migrate sftp> ls grantfile grantifle myfile query.sql sftp> lcd /usr/local/src sftp> lpwd Local working directory: /usr/local/src sftp> get grantfile Fetching /home/dan/migrate/grantfile to grantfile /home/dan/migrate/grantfile 100% 8146 8.0KB/s 00:00 sftp> quit #
Now we have the granfile on the new server, in the directory /usr/local/src
Execute that command file in MySQL on the new server, and our users and permissions will be generated in the new server.
mysql -uroot -p < ./grantfile
I ran into some problems with a couple of the lines. The errors were written to the screen. I had to go into the ‘grantfile’ and delete those lines (on inspection, I determined that I did not need the permissions granted with the error lines).
Now, the users and permissions are setup, I need to migrate the schema…
Since I have multiple schema (about 50 or so), I decided to migrate them 1 at a time, using MySQL Workbench. The Workbench does the heavy lifting (writing the sql for the transfer, and either running it with connections to both servers, or outputting an sql file that will run and migrate the data. I used it in ‘live mode’, where it created the schema, and then moved each table one at a time.
I did run into a couple of problems on the new server, where allowable defaults in the v 5.0 of MySQL were not allowed in the new version 5.7 of MySQL. The migration wizard allows you to edit the conversion script mid-process. In the end, I went back into the v5.0, changed the defaults for TIMESTAMP to an allowable value in MySQL 5.7, and then the migration worked flawlessly.
Leave a Reply