#mysql #cli MySQL command line commands

mysqlcmd.sh

#export

#export directly to a diff server
#ref: https://twitter.com/fideloper/status/1009781805581553665/photo/1
mysqldump --single-transaction some_db \
| gzip | ssh user@host "cat > some_db.sql.gz"

#export directly to S3
mysqldump --single-transaction some_db | gzip | aws s3 cp - s3://some-bucket/some_db.sql.gz

mysqldump -uuser -p database_name > database_name.sql

mysqldump --no-data -uusername -p the-database > dump_file # database structure only:
mysqldump --no-create-info -uusername -p the-database > dump_file #database data only:

mysqldump -uuser -p --databases database_one database_two > two_databases.sql
mysqldump -uuser -p --all-databases > all_databases.sql

mysqldump -uuser -p --add-drop-database database_name > database_name.sql

#docker
# Backup
docker exec CONTAINER /usr/bin/mysqldump -u root --password=root DATABASE > backup.sql
docker exec some-mysql sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /some/path/on/your/host/all-databases.sql

# Restore
cat backup.sql | docker exec -i CONTAINER /usr/bin/mysql -u root --password=root DATABASE


#import
mysql -uuser -p --one-database database_name < all_databases.sql

mysqldump db_name | gzip > db_name.sql.gz #zip and dump single table
gunzip < db_name.sql.gz | mysql -u username -p db_name #unzip and import in one line

#Show valid users on the MySQL Server
select host, user, password from mysql.user;

#Create a valid username
create user '<username>'@'<allowed hosts>' identified by '<user's password>';

#Allow a valid username to connect to a database
grant all on <database name>.* to '<username>'@'<allowed hosts>';

#Change a user's valid connecting hosts
update mysql.user set host=’<allowed hosts>’ where user=’<username>’;

#Change the valid connecting hosts on a database
update mysql.db set Host='<allowed host>' where Db='<database name>';

#Change the valid connecting hosts on a database
update mysql.db set Host='<allowed host>' where  Db='<database name>';

#Delete a valid user from the MySQL Server
delete from mysql.user where user='<username>';
delete from mysql.db where user='<username>';

#Flush All the MySQL Privileges (done after any modifications typically)
flush privileges;