MySQL basic, tricks and techniques

Temp backup table

create table session_content_backup LIKE session_content // creates a backup table with the same schema
insert into session_content_backup SELECT * FROM session_content // copy the existing data over to the new backup table
truncate session_content //deletes the existing data, while preserving data in the backup
//DO YOUR UPDATE/INsert here.
drop table session_content_backup //cleanup time.

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;