mysql> SELECT * FROM mysql.user;
mysql> DELETE FROM mysql.user WHERE user = ' ';
mysql> DELETE FROM mysql.user WHERE NOT (host="localhost" AND user="root");
mysql> UPDATE mysql.user SET user="mydbadmin" WHERE user="root";
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass';
mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;
mysql> UPDATE mysql.user SET password=oldpass("newpass") WHERE User='username';
mysql> DELETE FROM mysql.user WHERE user="username";
mysql> SHOW DATABASES;
mysql> CREATE DATABASE mydatabase;
mysql> USE mydatabase;
mysql> DROP DATABASE mydatabase;
mysqldump -u dbauser -pmypass databasename > mybackup.sql mysql -u dbauser -pmypass newdatabasename < mybackup.sql
mysql> SELECT * FROM queries ORDER BY query_id DESC LIMIT 200;
mysql> SHOW TABLES;
mysql> SELECT * FROM tablename;
mysql> RENAME TABLE first TO second; hoặc mysql> ALTER TABLE mytable rename as mynewtable
mysql> DROP TABLE mytable;
mysql> DESC mytable; hoặc mysql> SHOW COLUMNS FROM mytable;
mysql> UPDATE mytable SET mycolumn="newinfo" WHERE mycolumn="oldinfo";
mysql> SELECT * FROM mytable WHERE mycolumn='mydata' ORDER BY mycolumn2;
mysql> INSERT INTO mytable VALUES('column1data','column2data','column3data','column4data','column5data','column6data','column7data','column8data','column9data');
mysql> DELETE FROM mytable WHERE mycolumn="mydata";
backup: mysqldump --all-databases > alldatabases.sql restore: mysql -u username -pmypass < alldatabases.sql (no space in between -p and mypass)
Với bzip2: mysqldump --all-databases | bzip2 -c > databasebackup.sql.bz2 (dùng bunzip2 để giải nén) Với gzip: mysqldump --all-databases | gzip > databasebackup.sql.gz (dùng gunzip để giải nén)
#!/bin/sh date=`date -I` mysqldump --all-databases | gzip > /var/backup/dbbackup-$date.sql.gz
mysqldump -u username -pmypass databasename > backupfile.sql
mysqldump --no-data --databases databasename > structurebackup.sql
mysqldump --add-drop-table -u username -pmypass databasename table_1 table_2 > databasebackup.sql