In this tutorial I will show you how to reload SQL-format backups on Ubuntu Server. You will see how to reload SQL-format backups generated with mysqldump. During this tutorial you will learn how to properly use mysql client and mysqladmin.
Code used during this tutorial:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
# MySQL Reloading SQL-Format Backups on Ubuntu Server # MySQL Workbench # To reload a dump file written by mysqldump that consists of SQL statements, use it as input to the mysql client. # If the dump file was created by mysqldump with the --all-databases or --databases option, it contains # CREATE DATABASE and USE statements and it is not necessary to specify a default database into which # to load the data. # Dumps all tables structure and data from db1 database. mysqldump -u usrmysql -ppwd-mysql-usrmysql --databases db1 > /vagrant/share/mysqldump/mysqldump-01.sql # ERROR 1045 (28000): Access denied for user 'vagrant'@'localhost' (using password: NO) mysql < /vagrant/share/mysqldump/mysqldump-01.sql # MySQL Workbench: Make changes to db1.tbl1 and run the command below mysql -u usrmysql -ppwd-mysql-usrmysql < /vagrant/share/mysqldump/mysqldump-01.sql # Drop `db1` database # -f - Continue even if an SQL error occurs # Check MySQL Workbench mysqladmin -u usrmysql -ppwd-mysql-usrmysql -f drop db1 # Check MySQL Workbench mysql -u usrmysql -ppwd-mysql-usrmysql < /vagrant/share/mysqldump/mysqldump-01.sql # Dumps all tables structure and data from db1 database mysqldump -u usrmysql -ppwd-mysql-usrmysql db1 > /vagrant/share/mysqldump/mysqldump-02.sql # ERROR 1046 (3D000) at line 22: No database selected mysql -u usrmysql -ppwd-mysql-usrmysql < /vagrant/share/mysqldump/mysqldump-02.sql # MySQL Workbench: Make changes to db1.tbl1 and run the command below mysql -u usrmysql -ppwd-mysql-usrmysql db1 < /vagrant/share/mysqldump/mysqldump-02.sql # Drop `db1` database mysqladmin -u usrmysql -ppwd-mysql-usrmysql -f drop db1 # ERROR 1049 (42000): Unknown database 'db1' mysql -u usrmysql -ppwd-mysql-usrmysql db1 < /vagrant/share/mysqldump/mysqldump-02.sql # Create `db1` database mysqladmin -u usrmysql -ppwd-mysql-usrmysql create db1 mysql -u usrmysql -ppwd-mysql-usrmysql db1 < /vagrant/share/mysqldump/mysqldump-02.sql # Alternatively, reloading SQL-Format Backups can be made from within mysql mysql -u usrmysql -ppwd-mysql-usrmysql CREATE DATABASE IF NOT EXISTS db1; USE db1; source /vagrant/share/mysqldump/mysqldump-02.sql # Man page man mysql man mysqladmin mysqldump --help | less man mysqldump # Useful links https://github.com/liviubalan/liviubalan.com-vagrant-ubuntu http://dev.mysql.com/doc/refman/5.7/en/mysql.html http://dev.mysql.com/doc/refman/5.7/en/mysqladmin.html http://dev.mysql.com/doc/refman/5.7/en/using-mysqldump.html http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/ |
Useful links:
https://github.com/liviubalan/liviubalan.com-vagrant-ubuntu
http://dev.mysql.com/doc/refman/5.7/en/mysql.html
http://dev.mysql.com/doc/refman/5.7/en/mysqladmin.html
http://dev.mysql.com/doc/refman/5.7/en/using-mysqldump.html
http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/