MySQL Administration

Creating username with password and grant ALL access to dbname only accessible from localhost:

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost' WITH GRANT OPTION;

Doing the add user above with one command line:

mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost' IDENTIFIED BY 'password';

You can substitute 'localhost' with '%' and this will allow you to access the database from remote machine.

To check the privileges of the user, use this command below:

mysql> SHOW GRANTS FOR 'username'@'localhost';

Showing all the grants for all users:

mysql> SELECT User,Host from mysql.user;

Delete user:

mysql> DROP USER 'username'@'hostname';

Change user password:

mysql> SET PASSWORD FOR 'username'@'location' = PASSWORD('newpassword');

Update user password:

bash$ mysqladmin -u username -p'oldpass' password 'newpass'

mysql> USE mysql;

mysql> UPDATE user SET password=PASSWORD("NEWPASSWORD") WHERE User='USERNAME';

mysql> FLUSH PRIVILEGES;

Update user host:

mysql> UPDATE user SET host='newhost' WHERE user='username' AND host='oldhost';

Creating database with utf8 character:

mysql> CREATE DATABASE dbname CHARACTER SET utf8;

bash$ mysqladmin -u username -p create dbname --default-character-set=utf8

bash$ mysql -u root -p -e "CREATE DATABASE dbname CHARACTER SET utf8;

Dropping database:

mysql> DROP DATABASE dbname;

bash$ mysqladmin -u username -p drop dbname

Dumping the entire database:

bash$ mysqldump -u root -p --single-transaction --all-databases -R > all.sql

Restoring the entire database:

bash$ mysql -u root -p < all.sql

Restoring a particular database from the entire database dump:

bash$ mysql -u root -p --one-database database_name < all.sql

Backup a particular table from the database:

bash$ mysqldump --add-drop-table -h mysqlhostserver -u mysqlusername -p databasename tablename tablename tablename | bzip2 -c > tables.bak.sql.bz2

The --add-drop-table add a DROP DATABASE statement before each CREATE DATABASE statement
The bzip2 -c after the | (pipe) means the backup is compressed on the fly

Query Examples:

mysql> SELECT assetId,startTime,title FROM Schedules S JOIN Assets

ON S.assetId=Assets.id

WHERE S.partnerId not in (SELECT id FROM Partners WHERE customerId=3 OR name='YouTube')

AND (S.assetId in (SELECT id FROM Assets WHERE (status!='deleted' and masterId!='NULL')))

AND (S.status='submitted' or S.status='fail' or S.status='externalsubmit' or S.status='externalpending')

AND (S.expireTime>=NOW()) AND (S.createdTime<=SUBDATE(NOW(),INTERVAL 2 HOUR))

AND (S.startTime<=ADDDATE(NOW(),INTERVAL 21 DAY));

The result from the query above is:

Page updated

Google Sites

Report abuse