MySQL basic, tricks and techniques

Copy SQL from one server to other

$ scp ./output/2018-05-29-foo.sql <user>@111.11.111.11:/var/www/project/2018-05-29-foo.sql

Table to CSV

$ 'mysql -h 111.11.111.11 -u <user> -p<password> -D <database_name>' -BAq < foo.sql > ./output/2018-05-29-foo.sql

Temp backup table

Using a temporary store of the old data in case your attempted update/insert business fails/introduces a bug:

# Creates a backup table with the same schema:
> create table session_content_backup LIKE session_content
# Copy the existing data over to the new backup table:
> insert into session_content_backup SELECT * FROM session_content
# Deletes the existing data, while preserving data in the backup:
> truncate session_content
#
# DO YOUR UPDATE/INSERT here!
#
# Clean-up time:
> drop table session_content_backup

Dump and load sql

# Dump:
$ mysqldump -u root db_name > dump.sql
# Load:
$ mysql -u root < dump.sql

Note: -p = prompt for password (default, so this option may be omitted). For no prompt, you can include the password like using -p<password> (no space between the -p and <!) This also works: --password=pass_val.

Misc.

Enter into mysql prompt using (in this case, root is the user):

$ mysql -u root -p
# Probably no password if using root, so just hit return.

Useful commands (enter into $ mysql command prompt):

# List databases:
> SHOW DATABASES;

# List mysql users:
> SELECT User FROM mysql.user;

# Create a user:
> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

# Create a database:
> CREATE DATABASE dbname;

# User permission for specific database:
> GRANT SELECT, INSERT, DELETE ON dbname.* TO 'user'@'localhost';

# When finished with permission changes:
> FLUSH PRIVILEGES;

# View grants:
> SHOW GRANTS FOR 'user'@'localhost';

# Import SQL:
> USE dbname;
> SET autocommit=0; source dbname.sql; COMMIT;

# List tables in database:
> USE dbname;
> SHOW TABLES;