Cacher is the code snippet organizer for pro developers

 
 
Dump and restore a single table from .sql

Dump
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:

http://www.abbeyworkshop.com/howto/lamp/MySQL_Export_Backup/index.html

Restore

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

Dump

mysqldump db_name table_name | gzip > table_name.sql.gz
Restore

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';
>FLUSH PRIVILEGES;

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.

Uninstalling:

sudo apt-get purge mysql-server
sudo apt-get autoremove
sudo apt-get autoclean
Re-Installing:

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