Link Search Menu Expand Document

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

  1. Create a new database

    CREATE DATABASE <new-database>;
    GRANT ALL ON <database-name>.\* TO '<user-name>'@'localhost';
    FLUSH PRIVILEGES;
    EXIT
    
  2. 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

  1. Note difference, using < for import and > for export

    mysql -u [username] -p [database-name] > [database-name].sql
        # one step outside mysql shell, check where is the file saved