There are several ways to upgrade MySQL. In this post, we will use a combination of shell scripts and the mysqldump application to export our MySQL data, and then re-import it back into the upgraded version of MySQL.
In this example, we will be doing a minor version upgrade. We will be going from 5.6.17 to 5.6.19. This method may not work if you are upgrading from one major release to another – from 5.1 to 5.5, or 5.5 to 5.6. You will want to check each version and review the new features/functions and also what features/functions have been deprecated. We are also assuming that no one will be using the database during the time it takes for us to do the upgrade.
If you want to upgrade from a version that is more than one major release apart from your current version, then you will want to upgrade to each successive version. For example, if you want to upgrade from 5.0 to 5.6, you will want to upgrade from 5.0 to 5.1, then 5.1 to 5.5, and then 5.5 to 5.6.
You don’t have to export all of your data when you upgrade MySQL. There are ways of upgrading without doing anything to your data. But in this post, I will be exporting the data and re-importing it, for a fresh installation. I don’t have that much data, so I don’t mind doing the export and import. If you have a lot of data, you might want to consider other options. To get an idea of the size of your database(s), here is a quick script that you can use:
SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ; When I perform an export/import, I like to export each database as a separate mysqldump file, and then also export all of the databases together in one large file. By exporting/importing the individual databases, if you have an error importing one of the database dump files, you can isolate the error to a single database. It is much easier to fix the error in one smaller data dump file than with a larger all-inclusive dump file.
I am also going to create some simple shell scripts to help me create the commands that I need to make this task much easier. First, you will want to create a directory to store all of the scripts and dump files. Do all of your work inside that directory.
Next, I want to get a list of all of my databases. I will log into mysql, and then issue the show databases; command: (which is the same command as: select schema_name from information_schema.schemata;)
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| 12thmedia |
| cbgc |
| comicbookdb |
| coupons |
| healthcheck |
| innodb_memcache |
| landwatch |
| laurelsprings |
| ls_directory |
| mem |
| mysql |
| performance_schema |
| protech |
| scripts |
| stacy |
| storelist |
| test |
| testcert |
| tony |
| twtr |
| watchdb |
+——————–+
22 rows in set (1.08 sec)
I can then just highlight and copy the list of databases, and put that list into a text file named “list.txt“. I do not want to include these databases in my export:
information_schema
mysql
performance_schema
test
However, I will export the mysql.user table later. I will need to manually remove those databases from my list.txt file. I then want to remove all of the spaces and pipe symbols from the text file – assuming that you do not have any spaces in your database names. Instead of using spaces in a database name, I prefer to use an underline character “_“. These scripts assume that you don’t have any spaces in your database names.
If you know how to use the vi editor, you can so a substitution for the pipes and spaces with these commands: :%s/ //g
:%s/|//g
Otherwise, you will want to use another text editor and manually edit the list to remove the spaces and pipe symbols. Your finished list.txt file should look like this:
12thmedia cbgc
comicbookdb
coupons
healthcheck
innodb_memcache
landwatch
laurelsprings
ls_directory
mem
protech
scripts
stacy
storelist
testcert
tony
twtr
watchdb
You can then create a simple shell script to help create your mysqldump commands – one command for each database. You will want to create this script and the other scripts in the directory you created earlier. Name the script export.sh. You can also change the mysqldump options to meet your needs. I am using GTID’s for replication, so I want to use this option –set-gtid-purged=OFF. You will also want to change the value of my password my_pass to your mysql password. You can also skip including the password by using the -p option, and just enter the password each time you run the mysqldump command.
# export.sh
# script to create the database export commands
k=""
for i in `cat list.txt`
do
echo "mysqldump -uroot –password=my_pass –set-gtid-purged=OFF –triggers –quick –skip-opt –add-drop-database –create-options –databases $i > "$i"_backup.sql"
k="$k $i"
done
# Optional – export the entire database
# use the file extention of .txt so that your script won’t import it later
echo "mysqldump -uroot –password=my_pass –set-gtid-purged=OFF –triggers –quick –skip-opt –add-drop-database –create-options –databases $k > all_db_backup.txt"
For the individual databases, I am using the suffix of .sql. For the dump file that contains all of the databases, I am using the prefix .txt – as I use a wildcard search later to get a list of the dump files, and I don’t want to import the one dump file that contains all of the databases.
Now you can run the export.sh script to create a list of your mysqldump commands, and you are going to direct the output into another shell script named export_list.sh. # sh export.sh > export_list.sh
We can now take a look at what is in the export_list.sh file
# cat export_list.sh
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases 12thmedia > 12thmedia_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases cbgc > cbgc_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases comicbookdb > comicbookdb_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases coupons > coupons_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases healthcheck > healthcheck_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases innodb_memcache > innodb_memcache_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases landwatch > landwatch_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases laurelsprings > laurelsprings_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases ls_directory > ls_directory_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases mem > mem_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases protech > protech_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases scripts > scripts_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases stacy > stacy_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases storelist > storelist_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases testcert > testcert_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases tony > tony_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases twtr > twtr_backup.sql
mysqldump -uroot –set-gtid-purged=OFF –password=my_pass –triggers –quick –skip-opt –add-drop-database –create-options –databases watchdb > watchdb_backup.sql
mysqldump -uroot -p –set-gtid-purged=OFF –password=my_psss –triggers –quick –skip-opt –add-drop-database –create-options –databases 12thmedia cbgc comicbookdb coupons healthcheck innodb_memcache landwatch laurelsprings ls_directory mem protech scripts stacy storelist testcert tony twtr watchdb > all_db_backup.txt
Now you have created a list of mysqldump commands that you can execute to dump all of your databases. You can now go ahead and execute your mysqldump commands by running the export_list.sh script:
# sh export_list.sh
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
….
The message “Warning: Using a password on the command line interface can be insecure.” is shown because you included the value for “–password“. If you don’t want to put your password on the command line, just change that option to “-p“, and you will have to manually enter your MySQL root user’s password after each mysqldump command.
Here is a list of the dump files that was produced:
# ls -l
total 21424
-rw-r–r– 1 root staff 26690 Aug 1 16:25 12thmedia_backup.sql
-rw-r–r– 1 root staff 5455275 Aug 1 16:26 all_db_backup.txt
-rw-r–r– 1 root staff 1746820 Aug 1 16:25 cbgc_backup.sql
-rw-r–r– 1 root staff 492943 Aug 1 16:25 comicbookdb_backup.sql
-rw-r–r– 1 root staff 1057 Aug 1 16:25 coupons_backup.sql
-rw-r–r– 1 root staff 3366 Aug 1 16:25 export_list.sh
-rw-r–r– 1 root staff 1077 Aug 1 16:25 healthcheck_backup.sql
-rw-r–r– 1 root staff 3429 Aug 1 16:25 innodb_memcache_backup.sql
-rw-r–r– 1 root staff 1815839 Aug 1 16:25 landwatch_backup.sql
-rw-r–r– 1 root staff 642965 Aug 1 16:25 laurelsprings_backup.sql
-rw-r–r– 1 root staff 660254 Aug 1 16:25 ls_directory_backup.sql
-rw-r–r– 1 root staff 1037 Aug 1 16:25 mem_backup.sql
-rw-r–r– 1 root staff 1057 Aug 1 16:25 protech_backup.sql
-rw-r–r– 1 root staff 2889 Aug 1 16:25 scripts_backup.sql
-rw-r–r– 1 root staff 11107 Aug 1 16:25 stacy_backup.sql
-rw-r–r– 1 root staff 4002 Aug 1 16:25 storelist_backup.sql
-rw-r–r– 1 root staff 1062 Aug 1 16:25 testcert_backup.sql
-rw-r–r– 1 root staff 4467 Aug 1 16:25 tony_backup.sql
-rw-r–r– 1 root staff 1042 Aug 1 16:25 twtr_backup.sql
-rw-r–r– 1 root staff 52209 Aug 1 16:25 watchdb_backup.sql
You will now want to dump your MySQL users, so you don’t have to recreate the users, passwords and privileges after the new install.
mysqldump -uroot –password=my_pass –set-gtid-purged=OFF mysql user > mysql_user_backup.txt
I am once again using the .txt prefix for this file.
After you execute the above command, make sure that the dump file was created:
# ls -l mysql_user_backup.txt
-rw-r–r– 1 root staff 9672 Aug 1 16:32 mysql_user_backup.txt
We have now finished exporting all of our data, including our user data. You will need to shutdown MySQL. You may use mysqladmin to shutdown your database, or here is a link on ways to shutdown MySQL.
# mysqladmin -uroot –password=my_pass shutdown
Warning: Using a password on the command line interface can be insecure.
Before continuing, you might want to check to make sure that the mysqld process isn’t still active.
# ps -ef|grep mysqld
0 18380 17762 0 0:00.00 ttys002 0:00.00 grep mysqld
You are now going to want to change the name of your mysql directory. This will give you access to the old directory in case the upgrade fails. For my OS (Mac OS 10.9), my MySQL home directory is a symbolic link to another directory that contains the actual MySQL data. All I have to do is to remove the symbolic link. A new symbolic link will be created with the new install. Otherwise, just use the mv command to rename your old MySQL directory.
# cd /usr/local/
# ls -ld mysql* lrwxr-xr-x 1 root wheel 36 Aug 9 2013 mysql -> mysql-advanced-5.6.17-osx10.6-x86_64
drwxr-xr-x 18 root wheel 612 Jan 16 2014 mysql-advanced-5.6.17-osx10.6-x86_64
All I have to do is to remove the link, and the MySQL directory will still be there:
# rm mysql
# ls -ld mysql* drwxr-xr-x 18 root wheel 612 Jan 16 2014 mysql-advanced-5.6.17-osx10.6-x86_64
Now I am ready to install the new version of MySQL. I won’t cover the installation process, but here is the link to the installation page.
Tip: After you have installed MySQL, don’t forget to run this script from your MySQL home directory. This will install your mysql database tables. Otherwise, you will get an error when you try to start the mysqld process.
# ./scripts/mysql_install_db
Now you can start the mysqld process. See this page if you don’t know how to start MySQL.
You can test to see if the new installation of MySQL is running by either checking the process table, or logging into mysql. With a fresh install of 5.6, you should not have to include a user name or password.
Note: (Future versions of MySQL may automatically create a random root password and put it in your data directory. You will then need to use that password to login to MySQL for the first time. Check the user’s manual for any MySQL versions beyond 5.6.)
# mysql
Welcome to the mysql monitor. Commands end with ; or \g.
Your mysql connection id is 3
….
mysql>
Now that MySQL is up and running, leave the mysql terminal window open, and open another terminal window so you can import your mysql user information from your dump file:
# mysql < /users/tonydarnell/mysql_2014_0731/2014_0731_mysql_backup.sql
You won’t be able to login with your old user names and passwords until you execute the flush privileges command. So, in your other terminal window with the mysql prompt:
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Open another terminal window and see if you can login with your old mysql user name and password:
# mysql -uroot -p
Enter password: Welcome to the mysql monitor. Commands end with ; or \g.
Your mysql connection id is 3
….
mysql>
You can then look at your the user names and passwords in the mysql.user table:
mysql> select user, host, password from mysql.user order by user, host;
+—————-+—————+——————————————-+
| user | host | password |
+—————-+—————+——————————————-+
| root | 127.0.0.1 | *BF6F71512345332CAB67E7608EBE63005BEB705C |
| root | 192.168.1.2 | *BF6F71512345332CAB67E7608EBE63005BEB705C |
| root | 192.168.1.5 | *BF6F71512345332CAB67E7608EBE63005BEB705C |
| root | 192.168.1.50 | *BF6F71512345332CAB67E7608EBE63005BEB705C |
| root | localhost | *BF6F71512345332CAB67E7608EBE63005BEB705C |
+—————-+—————+——————————————-+
5 rows in set (0.00 sec)
OPTIONAL:
Since I am using GTID’s for replication, I can check to see how many transactions have been completed, by issuing the show master status command:
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 644455
Binlog_Do_DB: Binlog_Ignore_DB: coupons,usta,ls_directory,landwatch
Executed_Gtid_Set: e1eb3f38-18da-11e4-aa44-0a1a64a61679:1-124
1 row in set (0.00 sec)
We are now ready to import the database dump files. We can use this script to create the import commands. Copy this into a text file named import.sh:
# import.sh
# script to import all of the export files
# run this script in the same directory as the exported dump files
#
> import_files.sh
directory=`pwd`
for file in `ls *sql`
do
if [[ $(grep -c ‘.txt’ $file) != 0 ]];then
echo "# found mysql – do nothing"
else
echo "mysql -uroot -p"my_pass" < $directory/$file"
echo "mysql -uroot -p"my_pass" > import_files.sh
fi
done
Then run the import.sh script. The script will print the output to the terminal window as well as into a new script file named import_files.sh.
# sh import.sh
mysql -uroot -pmy_pass < 12thmedia_backup.sql
mysql -uroot -pmy_pass < cbgc_backup.sql
mysql -uroot -pmy_pass < comicbookdb_backup.sql
mysql -uroot -pmy_pass < coupons_backup.sql
mysql -uroot -pmy_pass < healthcheck_backup.sql
mysql -uroot -pmy_pass < innodb_memcache_backup.sql
mysql -uroot -pmy_pass < landwatch_backup.sql
mysql -uroot -pmy_pass < laurelsprings_backup.sql
mysql -uroot -pmy_pass < ls_directory_backup.sql
mysql -uroot -pmy_pass < mem_backup.sql
mysql -uroot -pmy_pass < protech_backup.sql
mysql -uroot -pmy_pass < scripts_backup.sql
mysql -uroot -pmy_pass < stacy_backup.sql
mysql -uroot -pmy_pass < storelist_backup.sql
mysql -uroot -pmy_pass < testcert_backup.sql
mysql -uroot -pmy_pass < tony_backup.sql
mysql -uroot -pmy_pass < twtr_backup.sql
mysql -uroot -pmy_pass < watchdb_backup.sql
Look at the contents of the new script file – import_files.sh – to make sure that it contains all of the database files. You will use this file to help you import your dump files.
# cat import_files.sh
mysql -uroot -pmy_pass < 12thmedia_backup.sql
mysql -uroot -pmy_pass < cbgc_backup.sql
mysql -uroot -pmy_pass < comicbookdb_backup.sql
mysql -uroot -pmy_pass < coupons_backup.sql
mysql -uroot -pmy_pass < healthcheck_backup.sql
mysql -uroot -pmy_pass < innodb_memcache_backup.sql
mysql -uroot -pmy_pass < landwatch_backup.sql
mysql -uroot -pmy_pass < laurelsprings_backup.sql
mysql -uroot -pmy_pass < ls_directory_backup.sql
mysql -uroot -pmy_pass < mem_backup.sql
mysql -uroot -pmy_pass < protech_backup.sql
mysql -uroot -pmy_pass < scripts_backup.sql
mysql -uroot -pmy_pass < stacy_backup.sql
mysql -uroot -pmy_pass < storelist_backup.sql
mysql -uroot -pmy_pass < testcert_backup.sql
mysql -uroot -pmy_pass < tony_backup.sql
mysql -uroot -pmy_pass < twtr_backup.sql
mysql -uroot -pmy_pass < watchdb_backup.sql
WARNING: Be sure that this script file does not contain the main dump file or the mysql user’s file that we created.
I was exporting and importing eighteen (18) database files, so I can also check the line count of the import_files.sh script to make sure it matches:
# wc -l import_files.sh
18 import_files.sh
I am now ready to import my files.
Optional: add the -v for verbose mode – sh -v import_files.sh
# sh import_files.sh
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
….
You databases should now be imported into your new instance of MySQL. You can always re-run the script to make sure that the databases are the same size.
OPTIONAL:
Since I am using GTID’s for replication, I can check to see how many transactions have been completed after importing the dump files, by issuing the show master status command:
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 16884001
Binlog_Do_DB: Binlog_Ignore_DB: coupons,usta,ls_directory,landwatch
Executed_Gtid_Set: cc68d008-18f3-11e4-aae6-470d6cf89709:1-43160
1 row in set (0.00 sec)
Your new and fresh installation of MySQL should be ready to use.
Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
via Planet MySQL
Upgrade MySQL to a new version with a fresh installation & use shell scripts and mysqldump to reload your data