3/22/2016 - 11:31 AM

mysql dump mysqldump install

mysql dump mysqldump dump cpmmand line copy table install

Dump and restore a single table from .sql

mysqldump db_name table_name > table_name.sql

Dumping from a remote database
mysqldump -u <db_username> -h <db_host> -p db_name table_name > table_name.sql
For further reference:


mysql -u <user_name> -p db_name
mysql> source <full_path>/table_name.sql
or in one line

mysql -u username -p db_name < /path/to/table_name.sql

Dump and restore a single table from a compressed (.sql.gz) format
Credit: John McGrath


mysqldump db_name table_name | gzip > table_name.sql.gz

gunzip < table_name.sql.gz | mysql -u username -p db_name
 mysql -u root -p mydb < table_file.sql

gunzip -k filename.gz
mysqldump -u root -p db_name table_name --single-transaction | gzip > table_name.sql.gz
mysqldump -u root -p db_name  --single-transaction | gzip > db_name.sql.gz
mysqldump -u mydb -p user | gzip > my_table.sql.gz
 You can accomplish this using the mysqldump command-line function.

For example:

If it's an entire DB, then:

   $ mysqldump -u [uname] -p[pass] db_name > db_backup.sql
If it's all DBs, then:

   $ mysqldump -u [uname] -p[pass] --all-databases > all_db_backup.sql
If it's specific tables within a DB, then:

   $ mysqldump -u [uname] -p[pass] db_name table1 table2 > table_backup.sql
You can even go as far as auto-compressing the output using gzip (if your DB is very big):

   $ mysqldump -u [uname] -p[pass] db_name | gzip > db_backup.sql.gz
If you want to do this remotely and you have the access to the server in question, then the following would work (presuming the MySQL server is on port 3306):

   $ mysqldump -P 3306 -h [ip_address] -u [uname] -p[pass] db_name > db_backup.sql
It should drop the .sql file in the folder you run the command-line from.

#install mysql
apt install mysql-server

#To check whether the MySQL server is running, type:
systemctl status mysql

>sudo mysql
>ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'very_strong_password';

CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'very_strong_password';

The second, recommended option is to create a new administrative user with access to all databases:
GRANT ALL PRIVILEGES ON *.* TO 'administrator'@'localhost' IDENTIFIED BY 'very_strong_password';

mysql is client
mysqld is the server
Try: sudo service mysqld start

To check that service is running use: ps -ef | grep mysql | grep -v grep.


sudo apt-get purge mysql-server
sudo apt-get autoremove
sudo apt-get autoclean

sudo apt-get update
sudo apt-get install mysql-server

to solve error: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
 mysql -u root -p[Enter]
 //enter your localhost password

 GRANT ALL PRIVILEGES ON d9.* to 'd9'@'localhost';

EDIT: As noted in comments, to avoid inclusion of your password in your command history, use the -p option without the password. It will prompt you for it and not record it.
 mysqldump -u root -p db_name > /root/drush-backups/archive-dump/dbname_full_mysql_2016-03-22.sql