Several new features have been added to the MySQL 8 including, JSON support, Transactional Data Dictionary, Persistent runtime configuration, Document Store, Optimizer Hints, SQL roles, CTEs, window functions, Invisible indexes, and many more. Show
What you will learn? In this tutorial, you will learn some stuff related to the MySQL server, and below are details about:
Prerequisites We will install the MySQL Server 8.04 on the latest Debian Buster 10 with 2GB of RAM and 4 CPUs for this guide. Also, we need the root privilege for installing packages and editing system configurations. Step 1 - Add MySQL RepositoryFor this tutorial, we will install the MySQL Server 8 from the official MySQL repository. To do that, we need to add the official MySQL repository to our Debian Server. Before adding the MySQL repository, install the 'GnuPG' package using the apt command below. sudo apt install gnupg -y Once all installation is completed, download the MySQL APT repository and add it to your system. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb Leave the repository as default and select 'OK'. And the official MySQL repository has been added. Next, update all available repositories on the Debian system using the following command. sudo apt update And you will get the official MySQL repository on the list below. As can be seen, the official MySQL repository has been added, and we're ready to install the MySQL Server 8.0 to the Debian Buster 10. Step 2 - Install MySQL ServerTo install the MySQL Server 8.0 to the Debian system, run the apt command below. sudo apt install mysql-server Now type the root password for your MySQL installation and choose 'OK'. Repeat the password for your MySQL root user.Advertisement After that, you will be asked to configure the MySQL default authentication plugin. Select the 'Strong Password Encryption' as recommended and choose 'OK'. And the MySQL Server 8.0 installation is finished. Now check the MySQL service using the following command. systemctl is-enabled mysql Below is the result you will get. As a result, the MySQL service is up and running on the Debian Server. The service will be running automatically at every system boot. Step 3 - Secure MySQL Deployment using mysql_secure_passwordIn this step, we will improve the MySQL server installation's security using the 'mysql_secure_installation' command-line tool. The 'mysql_secure_installation' tool will help you improve your MySQL deployment from different aspects such as setup the password strength, disable remote access, and removing default anonymous user and test databases. Now run the 'mysql_secure_installation' command on your shell. mysql_secure_installation At the beginning of the MySQL installation, you've already configured the root password for MySQL. Now type the password to continue to the process. Enter the password for user root: TYPE YOUR MYSQL ROOT PASSWORD After that, type 'Y' to configure the 'VALIDATE PASSWORD' component. VALIDATE PASSWORD COMPONENT can be used to test passwords Now choose the password validation policy level for all users. Type '2' to set up the 'STRONG' password level policy. There are three levels of password validation policy: Now 'mysql_secure_installation' command will check your current password strength. As can be seen, the strength of our current password is '50'. Now type 'Y' to change the current password with a new stronger password. Estimated strength of the password: 50 Now type your strong password and make sure to have the score 100, then type 'Y' to continue with the new password. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb0 As a result, the MySQL component 'VALIDATE PASSWORD' has been configured with a strong password policy, and the new MySQL password has been created. Next, you will be asked to remove the default Anonymous user. Type 'Y' to remove the default anonymous user for your installation. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb1 Next, type 'Y' again to disable MySQL remote login and if you want the MySQL server accessible only locally. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb2 After that, remove the default database 'test' from your MySQL Server. Type 'Y' and press Enter to continue. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb3 Next, type 'Y' again to reload the privilege tables. In effect, and new MySQL configuration changes have been applied. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb4 As a result, the Secure MySQL Server Deployment using te 'mysql_secure_installation' script has been completed. And at this stage, you've enabled the 'VALIDATE PASSWORD' component with the level '2' strong password policy, also the new strong root password for MySQL has been configured. Step 4 - Secure MySQL Server Using Additional ConfigurationFor this tutorial, we will add additional configuration to improve the deployment of the MySQL Server. We will specify the MySQL Server's bind-address, change the default port, disable load data file, and set up a password expiration policy for all MySQL users. Now go to the '/etc/mysql/conf.d/' directory and create a new configuration 'mysqld.cnf' using vim editor. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb5 Paste the following configuration into it. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb6 Save and close. Next, restart the MySQL service and check open ports on the system using the following command. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb7 Below is the result you will get. As can be seen, the MySQL Server is up and running on the local IP address '127.0.0.1' on the non-default TCP port '8320'. Next, to log in to the MySQL shell with the custom port, you can use the 'mysql' command with '-P port' option as below. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb8 Type the root password for the MySQL Server. Once you've logged in, run the following query to check the MySQL Server's port configuration. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb9 And you will get the MySQL Server is running on the custom port '8320' as below. As a result, you've configured the MySQL Server to be running on the local IP address '127.0.0.1' with the custom TCP port '8320'. Step 5 - Secure and Enable MySQL Remote Connection using SSLSuppose you want to deploy the MySQL Server on the internal network and allow other hosts on the same network to access databases on the MySQL Server. In that case, it's recommended to enable SSL for your MySQL Server deployment. In this step, you will learn how to set up and enable SSL and remote access on the MySQL Server. Generate SSL CertificatesBy default, the MySQL Server 8.0 is generated SSL certificates automatically during the installation. All default SSL certificates are available at the '/var/lib/mysql' directory. For this step, we will generate a new SSL certificate for the MySQL Server using the 'mysql_ssl_rsa_setup' command-line tool. Now create a new SSL directory '/etc/mysql/certs' and change that directory's ownership to the user 'mysql'. sudo apt update0 Next, generate new SSL certificates for the MySQL Server using the following command. And make sure to change the '--suffix=STRING' with your Common Name. sudo apt update1 Once it's completed, new SSL certificates for the MySQL Server has been generated at the '/etc/mysql/certs' directory. Check your SSL certificates using the following command. sudo apt update2 Below is the result you will get. The new SSL certificates for MySQL Server has been generated to the '/etc/mysql/certs/' directory. Enable SSL and Remote Access MySQL ServerTo enable the SSL and remote access on the MySQL Server, you will need to change the 'bind-address' option with your internal network's IP address and add the SSL configuration for MySQL. Now go to the '/etc/mysql/conf.d/' directory and edit the configuration that we've created on top 'mysqld.cnf' using vim editor. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb5 Change the 'bind-address' with your internal IP address as below. sudo apt update4 Next, add the SSL configuration for the MySQL Server below and change the SSL path with your own. sudo apt update5 Additionally, you can specify the TLS version of al your clients and force them to use only the secure SSL connection using the following configuration. sudo apt update6 Save and close. Next, restart the MySQL service to apply the new configuration and check the MySQL server's IP address using the following command. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb7 And you will get the MySQL Server is up and running on the internal network IP address with SSL enabled on it. Setup Client and Test SSL ConnectionTo connect to the MySQL Server from another host/client, you must create a new MySQL user, copy the client certificates, and add the MySQL configuration for the client. Log in to the MySQL shell using the following command. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb8 Now create a new MySQL user for remote connection using the following query. sudo apt update9 Type 'exit' to log out from the MySQL shell. Next, you need to copy the CA and client certificate and key to the client machine using the SCP command as below. sudo apt install mysql-server0 After that, connect to your client machine and create a new configuration using the vim editor. sudo apt install mysql-server1 Paste the MySQL client configuration as below and change the client SSL certificate path with your own. sudo apt install mysql-server2 Save and close. To connect to the remote MySQL Server with a custom port, run the 'mysql' command below. sudo apt install mysql-server3 Once you've logged in, check your connection status using the following query. sudo apt install mysql-server4 Below is the result you will get. As can be seen, you've logged in with SSL secure connection TLSv1.2 to the MySQL Server. As a result, the remote access with a secure SSL connection for MySQL Server has been enabled. Step 6 - Install and Configure phpMyAdminIn this step, you will learn how to install the latest phpMyAdmin with the Apache webserver. You will also learn how to connect to the MySQL Server on the remote host with a custom port and alose securely through SSL from the phpMyAdmin. Before going any further, install some packages dependencies using the apt command below. sudo apt install mysql-server5 Next, go to the '/var/www/html' directory and download the latest phpMyAdmin source code. sudo apt install mysql-server6 Now extract the phpMyAdmin source code and rename to the new directory named 'phpMyAdmin'. sudo apt install mysql-server7 Next, go to the '/var/www/phpMyAdmin' drectory and copy the default configuration file to 'config.inc.php'. sudo apt install mysql-server8 Edit the 'config.inc.php' configuration file using vim editor. sudo apt install mysql-server9 Generate the blowfish secret code using this link and paste the code to the 'blowfish_secret' line as below. systemctl is-enabled mysql0 Next, type your MySQL Server details under the 'Server Configuration' line as below. systemctl is-enabled mysql1 After that, paste the following configuration to enable the SSL communication from the phpMyAdmin to MySQL Server. And make sure to change the client and CA certificates' path with your own and make sure the user 'www-data' can access CA and client certificates. systemctl is-enabled mysql2 Save and close. Now change ownership of the 'phpMyAdmin' directory to the user 'www-data'. systemctl is-enabled mysql3 And the phpMyAdmin installation and configuration of phpMyAdmin has been completed. Now log in to the MySQL Server, check the MySQL SSL status, create a new database and user for phpMyAdmin. Log in to the MySQL Server using the following command. systemctl is-enabled mysql4 Now check the SSL status using the following queries. systemctl is-enabled mysql5 Below is the result you will get. As can be seen, the MySQL server is up and running with SSL enabled on it, and it's using SSL certificates that we've generated. We're also connected to the MySQL Server through the SSL secure connection using the '~/.my.cnf' configuration file. Next, create a new database and user named 'phpmyadmin' using the following query. systemctl is-enabled mysql6 Type 'exit' to log out from the MySQL Shell. Now open your web browser and type the server IP address with the path of 'phpMyAdmin' on the address bar. http://server-ip/phpMyAdmin/ On the phpMyAdmin Login page, type the user 'phpmyadmin' with the password that we've created on top and click the 'GO' button. And you will get the phpMyAdmin Dashboard as below. As a result, the installation and configuration of phpMyAdmin have been completed. Step 7 - Create MySQL User and DatabaseFor this step, you will learn the primary usage of MySQL queries for managing user, database, and the basic mysqldump command for database backup. Connect to MySQL Server and Check Connection StatusTo connect to the MySQL server, you can use the 'mysql' command line. And by default, all connections from the local MySQL Server will be using the Unix socket, which the SSL connection does not make connections any more secure. Connect to the MySQL Server with default configuration using the following command. systemctl is-enabled mysql7 If your MySQL Server is running on the custom port, use the '-P port' option below. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb8 If your MySQL server runs on the local network with the custom port, use the 'h host/IP Address' and the '-P port' options below. systemctl is-enabled mysql9 Once you've connected to your MySQL Server, run the following query to check the connection status. mysql_secure_installation0 And you will get the following result. And you're successfully connected to the MySQL Server. Create and Show DatabaseAfter connecting to the MySQL shell, you will learn how to create the MySQL database, select the database, create tables, and insert data. To create a new MySQL database, use the following query. mysql_secure_installation1 Now check all list databases on your MySQL server using the following query. mysql_secure_installation2 Next, you can switch and select the database using the following query. mysql_secure_installation3 Next, you can create a new table on your database using the 'CREATE TABLE' query as bellow. mysql_secure_installation4 Now you can use 'DESCRIBE table-name' to check all fields on your database. mysql_secure_installation5 And you will get all details fields on the table 'users'. Next, you can insert data manually to your table using the 'INSERT' query as below. mysql_secure_installation6 Now you've successfully inserted data to the MySQL database. Next, check all data on the table using the 'SELECT' query as below. mysql_secure_installation7 And you will get all data from your table. Create MySQL UserAfter creating the database, you will learn how to create a user on the MySQL Server and grant the user privileges to databases. The basic MySQL query for creating a user is by using the 'CREATE USER' query. Then you can grant privileges of the database to your user using the command 'GRANT' following by its options. And the last you must reload the grant tables to apply the new changes.
Create a new user named '[email protected]' with the password 'MyHakasePasswordMySQL39==' and allow all privileges of the 'hakase_db' to the user using the following query. mysql_secure_installation8 Additional information, the hostname '[email protected]' is a part of the account name. The '@localhost' only allows the user to connect from the local server. If not given, the default is '%' which allowing users from everywhere.
We will create a new user that can only log in from the specific host IP address and use the particular authentication plugin. We will create a new user named 'yume' connecting through the client IP '172.16.0.4' with the 'mysql_native_password' authentication plugin, and the user will be allowed to access the database 'yume_db' only. mysql_secure_installation9 As a result, the user 'yume' will only connect to the MySQL server with IP address '172.16.0.4'. Otherwise, the authentication of the user will be denied by the MySQL Server.
To force the user to use the SSL secure connection, you can use the 'REQUIRE SSL' option. If the secure connection fails, you can't connect to the MySQL Server (cannot establish the connections). For a more secure scenario, you can use the 'REQUIRE X509' query, which means the connection to the MySQL Server only established when the user/client machine provided the client and CA certificates. Create a new user named 'juice' with the hostname '%'. This hostname option '%' allows the user 'juice' to connect from any IP address, but the connection will be established when the client machine provided client and CA certificates. Enter the password for user root: TYPE YOUR MYSQL ROOT PASSWORD0 As a result, the 'juice' user can connect to the MySQL Server when the machine provided client and CA certificates. Show All Available user and GrantsAfter creating all users, check all users using the following queries. Enter the password for user root: TYPE YOUR MYSQL ROOT PASSWORD1 Now you will get details user, host, and the authentication plugins that are used by users. Next, check all privileges of users using the 'SHOW GRANTS' query as below. Enter the password for user root: TYPE YOUR MYSQL ROOT PASSWORD2 Now you will get detailed privileges of your users. As a result, you've learned the primary usage of MySQL Server, including create a database, create a table, insert data to the table, create and show users, and show details privileges of each user. Step 8 - Additional: Create Incremental Backup MySQL DatabaseFor this step, you will learn the primary usage of the 'mysqldump' command for backup all MySQL databases. The 'mysqldump' command-line allows you to backup all databases, specific or multiple databases, and backup tables. Before going any further, create the backup directory and go into it. Enter the password for user root: TYPE YOUR MYSQL ROOT PASSWORD3 And you're ready to create MySQL databases backup using the 'mysqldump' utility. Backup All DatabasesTo back up all databases on the MySQL Server, use mysqldump command with the '--add-databases' option below. Enter the password for user root: TYPE YOUR MYSQL ROOT PASSWORD4 Now you will get all backup of your databases to the single SQL file named 'alldbs.sql'. Backup Specific DatabaseTo back up the specific MySQL database, include your database name on the mysqldump command below. Enter the password for user root: TYPE YOUR MYSQL ROOT PASSWORD5 As a result, you will get the SQL backup file from the database 'hakase_db' named as 'backup_hakase_db.sql' on the backup directory. Backup Multiple DatabasesIf you want to create a backup of multiple databases (not all databases on the MySQL Server), use the '--databases' option below. Enter the password for user root: TYPE YOUR MYSQL ROOT PASSWORD6 As a result, the backup file for your two databases 'yume_db' and 'juice_db' has been created, named as 'multi-db-once.sql' file. Automatic Backup MySQL DatabaseTo set up an automatic backup for MySQL, we will be using the Linux 'cron'. We will create a new bash script for MySQL databases and run it automatically at a specific time using the Linux Cron. Before going any further, log in to the MySQL shell using the following command. wget -q http://repo.mysql.com/mysql-apt-config_0.8.15-1_all.deb8 Create a new backup user with some privileges using the following query. Enter the password for user root: TYPE YOUR MYSQL ROOT PASSWORD8 Now type 'exit' to log out from the MySQL shell. Next, create a new backup directory '/opt/backup/mysql' for storing all your database backup files. Enter the password for user root: TYPE YOUR MYSQL ROOT PASSWORD9 After that, go to the '/usr/local/sbin' directory and create a new bash file 'auto_backup.sh' using vim editor. VALIDATE PASSWORD COMPONENT can be used to test passwords0 Change the details user, password, and port with your own and paste the configuration into it. VALIDATE PASSWORD COMPONENT can be used to test passwords1 Save and close. Now make the 'auto_backup.sh' script executable and run the script for testing purposes. VALIDATE PASSWORD COMPONENT can be used to test passwords2 As a result, you will get your database backup files on the '/opt/mysql/backup-current-date' directory. Check using the following command. VALIDATE PASSWORD COMPONENT can be used to test passwords3 Below is the result you will get. As a result, you will get compressed files of your database backup, which means the script is working. Next, create a new cronjob that will be running the MySQL auto backup script 'auto_backup.sh' every day at the time '00:00'. Set the default editor to 'vim' and create a new cronjob as user root using the following command. VALIDATE PASSWORD COMPONENT can be used to test passwords4 Now paste the following configuration into it. VALIDATE PASSWORD COMPONENT can be used to test passwords5 Save and close. The new cronjob for user root has been created. Check all cronjob list for the root user using the following command. VALIDATE PASSWORD COMPONENT can be used to test passwords6 And you will get details cron configuration for the user root. As a result, you've successfully set up the automatic MySQL database backup using the bash script and Linux Cron. Finally, you've learned how to install and secure the MySQL Database Server on the Debian Buster 10 with phpMyAdmin web-based database management. Also, you've learned the basic MySQL query for managing users, create database and tables, and using the mysqldump utility for backup databases. How to install MySQL on Debian 11?The process involves the following steps:. Download MySQL repository Debian package.. Add MySQL APT repository.. Refresh the Debian 11 Repository cache.. Run command to install MySQL.. Start & enable Database server service.. Secure Database server.. How to setup MySQL in Debian?To add the MySQL APT repository to your system go to the repository download page and download the latest release package using the following command. Install the release package. We're going to install MySQL version 8.0. Select OK by pressing Tab and hit Enter (as shown in the image above).
How to install MySQL 8.0 on Debian?How to Install the Latest MySQL 8 on Debian 10 Buster. $ wget http://repo.mysql.com/mysql-apt-config_0.8.14-1_all.deb.. $ dpkg -i mysql-apt-config_0.8.14-1_all.deb.. $ apt update && apt install mysql-server -y.. $ service mysql status.. $ apt install -f.. $ apt update && apt autoremove && sudo apt -f install.. How to start MySQL server Debian 10?Once you've opened a terminal window, just type the following commands to start or stop MySQL server:. To start MySQL server: mysqld start.. To stop MySQL server: mysqld stop.. |