MySQL for Beginners
Table of contents
Installation
apt install mysql-server
mysql
Run MySQL
mysql
# or: sudo mysql -u root -p
MySQL for Wordpress
mysql> CREATE DATABASE <database-name> DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
# you cannot have `-` in the name;
# Query OK, 1 row affected, 2 warnings (0.01 sec)
mysql> CREATE USER '<user-name>'@'localhost' IDENTIFIED BY '<password>';
# Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL ON <database-name>.* TO '<user-name>'@'localhost';
# Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
# Query OK, 0 rows affected (0.00 sec)
mysql> EXIT
# Bye
Basic SQL command
- Note: the semicolon
;
at the end of command is important
mysql> SHOW DATABASES;
mysql> DROP DATABASE [database-name];
mysql> SELECT user, host FROM mysql.user;
# to check users
mysql> DROP USER '[database_user]'@'localhost';
mysql> DROP USER IF EXISTS '[user-name]'@'localhost';
# if host is `%`, just do `drop user 'user-name';`.
Remove and reinstall MySQL
apt-get remove -y mysql-\*
# once done do the 2nd command to purge
apt-get purge -y mysql-\*
apt install mysql-server
Making query
mysql> use [database-name];
mysql> select * from wp_users;
select * from wp_users\G
SQL to update Wordpress user password
UPDATE wp_users SET user_pass=md5('enter new password here') WHERE user_login='admin';
DELETE FROM `wp_comments` WHERE `comment_approved`='spam'
Import & export SQL
Import
-
Create a new database
CREATE DATABASE <new-database>; GRANT ALL ON <database-name>.\* TO '<user-name>'@'localhost'; FLUSH PRIVILEGES; EXIT
-
Import database into the new database just created
mysql -u [username] -p [new-database] < [database-name].sql # `database.sql` is a file exported from mysql
Export
-
Note difference, using
<
for import and>
for exportmysql -u [username] -p [database-name] > [database-name].sql # one step outside mysql shell, check where is the file saved