MySQL Tutorial¶
In this tutorial, we will be covering the basics of MySQL and how to use it. MySQL is a relational database management system (RDBMS) and is used to manage and store data. We will cover how to create and delete databases, tables, and columns, how to insert, view and delete data, how to create and delete users and grant privileges, how to backup and restore databases, and how to use the MyCLI. Getting Started with MySQL
Before you begin, you need to have MySQL installed on your system. You can download and install it from the official website. Once installed, you can start MySQL by running the following command:
Enter database¶
This command will prompt you for a password for the root user. If you haven't set up a password for the root user, press Enter. You should now be inside the MySQL shell. Creating and Deleting DatabasesCreate a new database,¶
use the CREATE DATABASE command followed by the name of the database you want to create:
List of all the databases¶
on your MySQL server, use the SHOW DATABASES command:
To use a specific database, use the USE command: To delete a database, use the DROP DATABASE command followed by the name of the database you want to delete: Creating and Deleting TablesTo create a new table, use the CREATE TABLE command followed by the name of the table and the list of columns:
mysql
CREATE TABLE table_name (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL
);
mysql
DROP TABLE table_name;
Inserting, Viewing, and Deleting Data
To insert data into a table, use the INSERT INTO command followed by the name of the table and the values to be inserted:
mysql
INSERT INTO table_name (username, password) VALUES ('user1', 'pass1');
To view the data in a table, use the SELECT command:
mysql
SELECT * FROM table_name;
To delete data from a table, use the DELETE command followed by the condition to be met:
mysql
DELETE FROM table_name WHERE username = 'user1';
Creating and Deleting Users
To create a new user, use the CREATE USER command followed by the username and password:
mysql
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
To grant privileges to a user, use the GRANT command followed by the privileges and the database and table names:
mysql
GRANT ALL PRIVILEGES ON myDB.* TO 'user'@'localhost'; GRANT SELECT ON table_name TO 'user'@'localhost';
To revoke privileges from a user, use the REVOKE command:
mysql
REVOKE ALL PRIVILEGES ON myDB.* FROM 'user'@'localhost';
Delete User
To delete a user from a MySQL database, you can use the DROP USER command followed by the username and the host. For example:
sql
DROP USER 'user'@'localhost';
To revoke privileges from a user, you can use the REVOKE command followed by the role or privilege you want to revoke. For example:
sql
REVOKE SELECT ON database.* FROM 'user'@'localhost';
Roles
To create a new role in MySQL, you can use the CREATE ROLE command followed by the role name. For example:
sql
CREATE ROLE role;
To grant a role to a user, you can use the GRANT command followed by the role name and the user/host. For example:
sql
GRANT role TO 'user'@'localhost';
To grant privileges to a role, you can use the GRANT command followed by the privilege and the database/table you want to grant access to. For example:
vbnet
GRANT SELECT ON database.* TO role;
To revoke a role from a user, you can use the REVOKE command followed by the role name and the user/host. For example:
sql
REVOKE role FROM 'user'@'localhost';
To see a list of databases in MySQL, you can use the SHOW DATABASES command. To see a list of users and their passwords, you can use the SELECT command on the mysql.user table. For example:
sql
SELECT host, user, password FROM mysql.user;
To see the port number that MySQL is running on, you can use the SHOW GLOBAL VARIABLES command followed by the variable name. For example:
sql
SHOW GLOBAL VARIABLES LIKE 'PORT';
To import a SQL file to an existing database, you can use the mysql command followed by the username, host, password, and database name. For example:
css
mysql -u [username] -h [host] -p [password] [database_name] < SQL file to import
To export and compress an existing MySQL database, you can use the mysqldump command followed by the username, host, password, and database name, piped to the gzip command. For example:
css
mysqldump -u [user] -h [host] -p [db_name] | gzip > [filename_to_compress.sql.gz]
To reset the root password in MySQL, you need to stop the MySQL service, start it with the --skip-grant-tables option to bypass authentication, update the mysql.user table with a new password for the root user, and restart the MySQL service. For example:
sql
service mysql stop mysqld_safe --skip-grant-tables & mysql UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE User='root';
To back up a MySQL database using mysqldump, you can use the following command, where wordpress is the name of the database, and xxx is the password for the root user:
perl
mysqldump -u root -p'xxx' wordpress | gzip -c > /backups/database/$(date +\%Y\%m\%d).sql.gz
To schedule the backup to run daily at 1am, you can add a cronjob using crontab -e and adding the following line:
bash
00 01 * * * mysqldump -u root -p'xxx' wordpress | gzip
Restoring MySQL Database
If you have a backup of your MySQL database and you want to restore it, you can use the following command:
css
mysql -u root –p'password' database_name < /path/to/[database_name].sql
Here, root is the username with privileges to restore the database, password is the password for the root user, and database_name is the name of the database that you want to restore. The path/to/ specifies the path to the backup file that you want to restore. Using mycli
mycli is a command-line interface for MySQL that offers auto-completion and syntax highlighting. It can be installed using pip:
pip install mycli
You can use mycli to connect to a MySQL database with an SSH tunnel by following these steps:
Set up an SSH tunnel to your MySQL server using the following command:
css
ssh -Nf root@serverip -L 3306:localhost:3306
Here, serverip is the IP address or hostname of your MySQL server.
Open mycli with the following command:
css
mycli -h localhost -u user -D myDB -p 3306
Here, user is the MySQL username that you want to use to connect to the database, myDB is the name of the database that you want to connect to, and 3306 is the port number that your MySQL server is listening on. Installing and Using PostgreSQL
To install PostgreSQL on Ubuntu, you can use the following command:
sudo apt install postgresql postgresql-contrib
After installing PostgreSQL, you can connect to the PostgreSQL server using the following command:
bash
psql -h $domain -U upadmin -d defaultdb -p 11550
Here, $domain is the hostname or IP address of your PostgreSQL server, upadmin is the username that you want to use to connect to the server, defaultdb is the name of the database that you want to connect to, and 11550 is the port number that your PostgreSQL server is listening on. Resetting WordPress Login Credentials
If you have lost the login credentials for your WordPress site and want to reset them directly from the database, you can use the following SQL commands:
go
INSERT INTO wp_users (user_login, user_pass, user_nicename, user_email, user_status) VALUES ('admin999', MD5('password999'), 'firstname lastname', '[email protected]', '0');
This will create a new user with the username admin999 and the password password999. You can then use this user to log in to your WordPress site.
sql
INSERT INTO wp_usermeta (umeta_id, user_id, meta_key, meta_value) VALUES (NULL, (Select max(id) FROM wp_users), 'wp_capabilities', 'a:1:{s:13:"administrator";s:1:"1";}');
This command will set the wp_capabilities meta key for the new user, giving them administrator privileges.
sql
INSERT INTO wp_usermeta (umeta_id, user_id, meta_key, meta_value) VALUES (NULL, (Select max(id) FROM wp_users), 'wp_user_level', '10');
This command will set the wp_user_level meta key for the new user, giving them the highest user level.