Category: MySQL

Ref: http://askubuntu.com/questions/750498/mysql-5-5-update-to-mysql-5-7

Here is what I’ve done:
DON’T FORGET TO BACKUP YOUR DATABASE FIRST! Pls read : http://myprojects.advchaweb.com/index.php/2017/03/18/script-to-backup-each-mysql-database-in-separate-files/

1. Download MySQL Apt Repository from https://dev.mysql.com/downloads/repo/apt/. Currently the file name is ‘mysql-apt-config_0.8.3-1_all.deb’.
   SORRY, ONLY ADMIN CAN SHOW THIS!
2. Install it:
   
   sudo dpkg -i mysql-apt-config_0.8.3-1_all.deb

   1	sudo dpkg -i mysql-apt-config_0.8.3-1_all.deb

3. Then I’d got this screenSelect ‘MySQL Server (Currently selected: mysql-5.7)’ then ‘ok’.
   Then I’d got the second screen.Select ‘mysql-5.7’ then ‘ok’. Then if you get to the first screen again, just select ‘Ok’ and ‘ok’. The screen would be closed.
4. Update my ubuntu system
   
   sudo apt-get update

   1	sudo apt-get update

5. install the MySQL-server package, which now contains MySQL 5.7
   
   sudo apt-get install mysql-server

   1	sudo apt-get install mysql-server

   
    
6. Now upgrade all mysql databases
   
   sudo mysql_upgrade -u root -p

   1	sudo mysql_upgrade -u root -p

   
    
7. Now restart mysql server
   
   teddy@teddy-K43SJ:/media/data/MASTER/MySQL$ sudo service mysql restart * Stopping MySQL Community Server 5.7.17 ... * MySQL Community Server 5.7.17 is stopped * Re-starting MySQL Community Server 5.7.17 No directory, logging in with HOME=/ .. * MySQL Community Server 5.7.17 is started

   1 2 3 4 5 6 7 8

   teddy@teddy-K43SJ:/media/data/MASTER/MySQL$ sudo service mysql restart * Stopping MySQL Community Server 5.7.17 ... * MySQL Community Server 5.7.17 is stopped * Re-starting MySQL Community Server 5.7.17 No directory, logging in with HOME=/ .. * MySQL Community Server 5.7.17 is started

   
    
8. Check mysql server version
   
   teddy@teddy-K43SJ:/media/data/MASTER/MySQL$ mysql --versionmysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper

   1	teddy@teddy-K43SJ:/media/data/MASTER/MySQL$ mysql --versionmysql  Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using  EditLine wrapper

   
    
9. Everything run well. At the phpmyadmin, I found the max upload size is 2M. I can modify this value in php.ini file (‘upload_max_filesize’).
   To find out where is the configuration file location for mysql, pls do like this:
   
   /usr/sbin/mysqld --help --verbose

   1	/usr/sbin/mysqld --help --verbose

   
   It’d show pretty long output, but I can found the line like this:
   
   Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

   1 2	Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

   
   SORRY, ONLY ADMIN CAN SHOW THIS!

Ref: http://stackoverflow.com/questions/9497869/export-and-import-all-mysql-databases-at-one-time

Here I want to backup all my database (more than 100 databases) in separate files. I can backup all the database in one single file like this:

But I want to pick only some of the databases to be imported on the different MySQL version and left out the rest (some of the databases I don’t want to be imported). From the top link, I create a new bash file ‘backup_each_db.sh’. Here is the code:

Make sure the ‘USER’ and ‘PASSWORD’ values for your MySQL server are correct!.
Then modify the file permission:

Then run the script:

SORRY, ONLY ADMIN CAN SHOW THIS!

To restore all the databases from the sql files, please run like this:
teddy@teddy:/media/teddy/Data/MASTER/MySQL/20180124_db_backup$ for SQL in *.sql; do DB=${SQL/\.sql/}; echo importing $DB; mysql -uroot -pYOURPASSWORD < $SQL; done
ref: https://stackoverflow.com/questions/4708013/import-multiple-sql-dump-files-into-mysql-database-from-shell
SORRY, ONLY ADMIN CAN SHOW THIS!