This process will take up to 15 minutes. Let’s get started!

Prerequisites

• A server running Ubuntu 24.04 OS
• User privileges: root or non-root user with sudo privileges

Update the System

We assume that you have a fresh installation of Ubuntu 24.04, so it is recommended that the packages be updated to their latest version before we take any actions on the server.

sudo apt update -y && sudo apt upgrade -y

Install MySQL server

Once, the system is up to date, we can proceed with the MySQL installation. To install MySQL on your server execute the following command:

sudo apt install mysql-server

After this command, you should allow some time for the installation to complete.

Manage the MySQL service

Once the installation is completed we can proceed with managing the MySQL service.

To start and enable the service:

sudo systemctl start mysql && sudo systemctl enable mysql

You should receive the following output:

root@host:# sudo systemctl start mysql && sudo systemctl enable mysql
Synchronizing state of mysql.service with SysV service script with /usr/lib/systemd/systemd-sysv-install.
Executing: /usr/lib/systemd/systemd-sysv-install enable mysql

To check the status of the service execute the command below:

sudo systemctl status mysql

You should get the following output:

root@host:~# sudo systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)
     Active: active (running) since Sat 2024-06-08 16:39:34 CDT; 2min 40s ago
   Main PID: 43182 (mysqld)
     Status: "Server is operational"
      Tasks: 37 (limit: 4613)
     Memory: 365.3M (peak: 379.7M)
        CPU: 2.968s
     CGroup: /system.slice/mysql.service
             └─43182 /usr/sbin/mysqld

Jun 08 16:39:32 host.test.vps systemd[1]: Starting mysql.service - MySQL Community Server...
Jun 08 16:39:34 host.test.vps systemd[1]: Started mysql.service - MySQL Community Server.

To restart the service you can use the following command:

sudo systemctl restart mysql

To stop the MySQL service:

sudo systemctl stop mysql

Create MySQL Database and User

Since we installed the MySQL database we will provide you with some simple tasks about creating a database and user in MySQL and assigning permissions between them. First log into the MySQL console with the following command:

mysql

You should see the following screen:

root@host:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36-2ubuntu3 (Ubuntu)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

To create a database execute the following command:

create database testdb;

After successful creation, you will get the following output:

mysql> create database testdb;
Query OK, 1 row affected (0.01 sec)

Now, let’s create a user:

create user testinguser@localhost IDENTIFIED BY 'StrongPasswordHere';

You will get the following output:

mysql> create user testuser;
Query OK, 0 rows affected (0.03 sec)

We have the database and the user. With the next command, we will grant access to the user on the database.

grant all ON testdb.* TO 'testinguser'@'localhost';
flush privileges;
 

There are many MySQL commands such as dumping and importing database, repairing tables, and optimizing MySQL configuration which will not be explained in this tutorial since it was only for the installation and the most common commands for creating database and user. Of course, you do not have to do this installation alone. You only need to sign up for our monthly server management or per-incident server support and submit a support ticket. Our admins will help you with any aspect of the MySQL installation and configuration on your server.

If you liked this post on how to install MySQL on Ubuntu 24.04, please share it with your friends and leave a comment below. Thanks.

The post How to Install MySQL on Ubuntu 24.04 appeared first on LinuxHostSupport.

MySQL is a popular open-source RDBMS, short for Relational Database Management System, widely used for web applications and other data-driven projects. It’s part of the LAMP/LEMP stack, and many popular websites and applications use it as a database system for storing data. However, users occasionally encounter issues where they can’t connect to the MySQL server, which can disrupt normally functioning applications. In this guide on how to fix MySQL can’t connect to server issues, we will explore common reasons for this problem and provide step-by-step solutions to help you resolve MySQL connection issues.

Check MySQL Server Status

Before diving into more complex troubleshooting, start by checking the status of your MySQL server. Ensure that the MySQL service is running and that there are no critical errors or warnings in the server logs. You can use the following command to check the MySQL service status:

# systemctl status mysql

You will receive similar output If the service is not running:

# systemctl status mysql
○ mariadb.service - MariaDB 10.6.12 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor prese>
     Active: inactive (dead) since Fri 2024-01-12 23:41:21 UTC; 1s ago

Start the MySQL service using:

# systemctl start mysql

Verify MySQL Port and Network Configuration

The Default MySQL port for connections is 3306. Ensure that the MySQL server is configured to listen on the correct port and that there are no firewall or network issues preventing connections. Check the MySQL configuration file (usually located at /etc/mysql/my.cnf or /etc/my.cnf) for the following lines:

bind-address = 127.0.0.1
port = 3306

Ensure that the bind-address allows connections from the necessary IP addresses or is set to 0.0.0.0 to allow connections from any IP address. To check connectivity to the MySQL server on port 3306 you can also use the netcat command. You can do that by running the command:

nc -zv 192.168.2.20 3306

Instead of 192.168.2.20, you should use the IP address of the MySQL server. If the connection is successful, you should get similar output:

Connection to 192.168.2.20 3306 port [tcp/mysql] succeeded!

If the connection fails, you will see an output like this:

nc: connect to 192.168.2.20 port 3306 (tcp) failed: Connection refused

Then, you should log in to the MySQL server and check which port is MySQL listening on.

MySQL User Privileges

Incorrect user privileges may prevent successful connections to the MySQL server. Verify that the user attempting to connect has the necessary privileges. You can use the following MySQL command to grant privileges:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Replace database_name, username, and password with your actual database name, username, and password. Then you can run the command:

FLUSH PRIVILEGES;

Then simply exit the MySQL shell and if the problem was with the MySQL user privileges you should be able to connect now.

Check for Network Issues

Network issues can often be a cause of connection problems. Ensure there are no network interruptions, and the server is accessible from the client machine. Ping the server to check for connectivity:

ping your_mysql_server_ip

If there are issues, check the network configuration, firewall settings, and any security groups or access control lists (ACLs) that may be blocking connections.

Examine MySQL Error Logs

MySQL logs provide valuable information about errors and warnings. Examine the MySQL error logs to identify any issues causing the connection problem. The error logs are typically located in the /var/log/mysql directory (check your MySQL configuration for the exact location) and are named error.log. You can find more information here that might help you identify the issue.

Resolving MySQL connection issues involves systematically identifying and addressing potential problems. By checking your server status, verifying port and network configurations, confirming user privileges, investigating network issues, and examining error logs, you can troubleshoot and fix MySQL can’t connect to server problems effectively. If you cannot solve this issue, you can always contact our Support Team, which will help you solve the problem without worrying too much about making the right changes.

The post How to Fix MySQL Can’t Connect to Server Issues appeared first on LinuxHostSupport.

Prerequisites

• A server with a Linux-based distribution (we used Ubuntu 22.04)
• User privileges: root or non-root user with sudo privileges

MySQL Can’t connect to local server

The error message “can’t connect to local MySQL server through socket” can appear due to various reasons. One possibility is that the server cannot establish a connection to the MySQL server using its default socket due to an incorrect path to the socket file. Other reasons include incorrect permissions, misconfiguration in the MySQL setup, or the MySQL service is crashing unexpectedly due to a broken database, not enough RAM on the server, etc.

The socket is an endpoint where two processes communicate with each other. When we say the MySQL socket, we are talking about the mysqld.sock file located at /var/run/mysqld/mysqld.sock. Let’s go over the possible solutions for this connecting issue with the local MySQL server.

Let’s go over each possibility – we’re confident your MySQL server will be accessible locally once you try all of these solutions.

Check Your MySQL Service

Sometimes the MySQL service can stop unexpectedly due to various reasons. It may be due to insufficient disk space, not enough RAM, or the server has been rebooted and the MySQL service was not enabled to start on boot previously.

So, when we try to log in to the MySQL console with the following command:

mysql -u root -p

The connection cannot be established, and we will receive the following output:

root@host:~# mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

First, we need to check if the MySQL service is up and running with the following command:

systemctl status mysql

If the service is not running, you should receive the following output:

root@host:~# systemctl status mysql
○ mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: inactive (dead)  since Mon 2024-01-08 18:19:37 CST; 13min ago
    Process: 30718 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
    Process: 30749 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
   Main PID: 30749 (code=exited, status=0/SUCCESS)
     Status: "Server shutdown complete"
        CPU: 1.981s

We just need to start and enable the service with the command below:

sudo systemctl start mysql && sudo systemctl enable mysql

After checking the status again, you should receive the following output:

root@host:~# systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Mon 2024-01-08 18:32:21 CST; 8min ago
   Main PID: 30950 (mysqld)
     Status: "Server is operational"
      Tasks: 37 (limit: 4558)
     Memory: 365.3M
        CPU: 5.241s
     CGroup: /system.slice/mysql.service
             └─30950 /usr/sbin/mysqld

Try to log into your server now – it should be working again. If you are still seeing the same error, move onto the next potential solution.

MySQL Socket Permissions

Let’s say that we started and enabled the MySQL service, but we are still experiencing the error message:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Now we need to check if the socket file exists and is in the right directory with the correct permissions. Execute the command below:

ls -al /var/run/mysqld/

If you get this output:

root@host:~# ls -al /var/run/mysqld/
drwxr-xr-x  2 root root 120 Jan 8 19:12 .
drwxr-xr-x 32 root root 960 Jan 8 19:12 ..
srwxrwxrwx  1 root root   0 Jan 8 19:12 mysqld.sock
-rw-------  1 root root   6 Jan 8 19:12 mysqld.sock.lock

This output shows us that the permissions are incorrect – the root user owns the files. In this case, we need to set the right permissions:

chown -R mysql:mysql /var/run/mysqld/
chmod 755 /var/run/mysqld/

Once the permissions are set, restart the MySQL service and try to connect again to the MySQL console.

MySQL Configuration File

If you are still not able to connect to the MySQL console, or in other words your MySQL client can’t connect to a local server, then you should check the MySQL configuration file usually located at /etc/mysql/mysql.conf.d/mysqld.cnf – other locations for this include /etc/mysql/my.cnf, or even /etc/my.cnf. Find where your configuration file is and open it using your preferred text editor.

Look for the socket variable and make sure to put the full path of the socket file. If there is no socket variable, add it to your file like so:

socket = /var/run/mysqld/mysqld.sock

Make sure the permissions for that folder are set correctly as well. Save the file, close it and restart the MySQL service. Your MySQL client should be able to connect to your local server.

If none of this is solving the issue and if you have a managed Linux support plan with us, you can ask our support team to help you with your MySQL service. We are available 24/7 and are experts in system configuration, including with MySQL. We will be able to help you with the configuration of MySQL as well as any other issues you’re having with your server.

The post How to Fix MySQL “Can’t connect to local server” appeared first on LinuxHostSupport.

MySQL is an open-source SQL database management system distributed and supported by Oracle Corporation. It is a relational database management system that provides fast, reliable, scalable, and easy usage. MySQL works in client/server or embedded systems.

MariaDB is also a popular open-source relational database management system made by the original MySQL developers. In this tutorial, we are going to install the MariaDB database system and will explain how to reset the root password. Since it is made by MySQL developers, the same commands are applied for both systems.

Installing MariaDB or MySQL and resetting the root password is a straightforward process that may take up to 15 minutes. Let’s get started!

Prerequisites

• A server with Ubuntu 22.04 as OS
• User privileges: root or non-root user with sudo privileges

Step 1. Update the System

Every fresh installation of Ubuntu 22.04 needs to be updated. That’s why we need to update the package to the latest versions available.

sudo apt-get update -y && sudo apt-get upgrade -y

Step 2. Install MariaDB database service.

To install the MariaDB database server, execute the command below.

sudo apt install mariadb-server -y

Start and enable the mariadb.service with the following commands:

sudo systemctl start mariadb && sudo systemctl enable mariadb

Check the status of the mariadb.service

sudo systemctl status mariadb

You should receive the following output:

root@host:~# sudo systemctl status mariadb 
● mariadb.service - MariaDB 10.6.12 database server 
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) 
Active: active (running) since Thu 2023-09-07 09:28:14 CDT; 17s ago 
Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ 
Main PID: 2722 (mariadbd) 
Status: "Taking your SQL requests now..." 
Tasks: 16 (limit: 4558) 
Memory: 61.2M 
CPU: 528ms 
CGroup: /system.slice/mariadb.service └─2722 /usr/sbin/mariadbd

Step 3. Secure MariaDB database service

Next we will secure the MariaDB database service and will set a root password. To do that execute the following command:

mysql_secure_installation

You need to pass these steps with the following options:

Switch to unix_socket authentication [Y/n] Y 
Change the root password? [Y/n] Y 
New password: 
Re-enter new password: 
Password updated successfully! 
Reloading privilege tables.. 
... Success! 
Remove anonymous users? [Y/n] Y 
Disallow root login remotely? [Y/n] Y 
Remove test database and access to it? [Y/n] Y 
Reload privilege tables now? [Y/n] Y 
All done! If you've completed all of the above steps, your MariaDB installation should now be secure. 
Thanks for using MariaDB

After installation login to the MySQL console will not ask for a password and will let you without any password, even though we set it in the previous step.

To set MySQL to ask you for a password execute the following command:

ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('YourStrongPasswordHere');

Now, if you try to log in only with this command: mysql you will get this message:

root@host:~# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

You need to use the following command and enter your password:

root@host:~# mysql -u root -p 
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 64 Server version: 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04 

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
MariaDB [(none)]>

Step 4. Reset MariaDB Root password

In the previous step we set the root password for the MariaDB root user to be YourStrongPasswordHere. We know our root password but in some cases, the admins or developers manage to lose it and need to log in to the MySQL server.

So, to set a new root password we must follow the next steps:

First stop the MariaDB service with the following command:

systemctl stop mariadb

After stopping the service check the status:

systemctl status mariadb

You should get the following output:

root@host:~# systemctl status mariadb 
○ mariadb.service - MariaDB 10.6.12 database server 
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) 
Active: inactive (dead) since Thu 2023-09-07 10:13:33 CDT; 36s ago 
Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ 
Process: 2722 ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=0/SUCCESS) 
Main PID: 2722 (code=exited, status=0/SUCCESS) 
Status: "MariaDB server is down" 
CPU: 1.760s

Now, when the service is stopped we need to start it without permission checking and without networking to prevent other users from connecting in the meantime:

sudo mysqld_safe --skip-grant-tables --skip-networking &

The ampersand will allow us to use the terminal. Now, execute the following command:

mysql -u root

This will connect us without a password. Flush the privileges first and then change the password with the commands below:

FLUSH PRIVILEGES; 
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewStrongPasswordHere'); EXIT;

Now, the password is changed from YourStrongPasswordHere TO NewStrongPasswordHere.

Next we need to kill the process id manually for the mysql service. To find it execute the following command:

ps aux | grep mysqld_safe

You will get output similar to this: root@host:~#

ps aux | grep mysqld_safe 
root 3292 0.0 0.1 14100 5596 pts/0 S 05:18 0:00 sudo mysqld_safe --skip-grant-tables --skip-networking 
root 3293 0.0 0.0 14100 880 pts/1 Ss+ 05:18 0:00 sudo mysqld_safe --skip-grant-tables --skip-networking 
root 3294 0.0 0.0 2888 1732 pts/1 S 05:18 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables --skip-networking 
root 3427 0.0 0.0 9208 2288 pts/0 S+ 05:28 0:00 grep mysqld_saf

In our case, the process id is 3292. To kill this process execute the following command:

kill -9 3292

Now, we can start the MariaDB service normally:

sudo systemctl start mariadb

Now, you can use the NewStrongPasswordHere.

Congratulations! You just learned how to reset MySQL or MariaDB Root password on Ubuntu 22.04. Of course, you do not have to do this if you find any difficulties. You can contact our technical support by submitting a support ticket or live chat. We are available 24/7

If you liked this post on how to reset your MySQL or MariaDB root password on Ubuntu 22.04, please share it with your friends on the social networks or simply leave a reply below. Thanks.

The post How to Reset Your MySQL or MariaDB Root Password on Ubuntu 22.04 appeared first on LinuxHostSupport.

When you hire a new developer to manage MySQL databases then you may need to grant specific permission to manage those databases such as deleting or modifying the information. In that case, it is essential for your to know how to grant specific privileges to the MySQL user account.

In this post, we will show you how to create a MySQL user and grant specific permissions

Prerequisites

• A Linux VPS with MySQL server installed.
• Access to the root user account (or access to an admin account with root privileges)

Log in to the Server & Update the Server OS Packages

First, log in to your Debian 10 server via SSH as the root user:

ssh root@IP_Address -p Port_number

You will need to replace ‘IP_Address’ and ‘Port_number’ with your server’s respective IP address and SSH port number. Additionally, replace ‘root’ with the username of the admin account if necessary.

Before starting, you have to make sure that all OS packages installed on the server are up to date. You can do this by running the following commands:

apt-get update -y
apt-get upgrade -y

Create a New MySQL User

First, you will need to connect to the MySQL shell using the MySQL root user. You can connect it using the following command:

mysql -u root -p

You will be asked to provide your MySQL root password. Once you are connected to the MySQL shell, you should see the following output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.25-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Now, create a new MySQL user with the following command:

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

Where:

• username is the name of the MySQL user you want to create.
• userpassword is the password of the MySQL user.
• localhost is a host from where you want to connect to MySQL.

You will need to replace the localhost with the remote server IP address if you want to connect the MySQL from the remote server. In that case, you can create a new MySQL user with the following command:

mysql> CREATE USER 'username'@'192.168.0.100' IDENTIFIED BY 'userpassword';

• 192.168.0.100 is the IP address of the remote server.

Grant Privileges to a MySQL User Account

There are multiple types of permissions available in MySQL that you can provide to the MySQL user account. Some of the most commonly used permissions are shown below:

• ALL PRIVILEGES: – This will allow MySQL users to run any query on the specified database.
• CREATE: – This will allow MySQL users to create databases and tables.
• DELETE: – This will allow MySQL users to delete rows from the table.
• DROP: – This will allow MySQL users to drop databases and tables.
• INSERT: – This will allow MySQL users to insert rows to a specific table.
• SELECT: – This will allow MySQL users to read a database.
• UPDATE: – This will allow MySQL users to update table rows.
• GRANT OPTION: – This will allow MySQL users to grant or remove other users’ privileges.

To grant all privileges to the MySQL user account on a specific database, run the following command:

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

To grant all privileges to the MySQL user on a specific table from a database, run the following command:

mysql> GRANT ALL PRIVILEGES ON dbname.tablename TO 'username'@'localhost';

To grant multiple privileges like, SELECT, INSERT, DELETE to the MySQL user on a specific database, run the following command:

mysql> GRANT SELECT, INSERT, DELETE ON dbname.* TO username@'localhost';

You will need to run the flush privileges command for the changes to take effect.

mysql> FLUSH PRIVILEGES;

View MySQL User Account Privileges

If you want to view the privileges assigned to the MySQL user account, run the following command:

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

You should see the following output:

+--------------------------------------------------------------+
| Grants for username@localhost                                |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `username`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `dbname`.* TO `username`@`localhost` |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

Remove MySQL User Account Privileges

You can use the REVOKE command to remove the permission of the MySQL user from the specific database or table.

For example, to remove ALL PRIVILEGES from the database, run the following command:

mysql> REVOKE ALL ON *.* FROM 'username'@'localhost';

Next, you will need to run the flush privileges command for the changes to take effect.

mysql> FLUSH PRIVILEGES;

Of course, you don’t have to manage MySQL server if you use one of our Managed Hosting services, in which case you can simply ask our expert Linux admins to manage the MySQL server for you. They are available 24/7 and will take care of your request immediately.

If you liked this post on How to Create a New User and Grant Privileges in MySQL, please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.

The post How To Create a New User and Grant Permissions in MySQL appeared first on LinuxHostSupport.

Before we start, make sure that you have full root access to your Linux server, or at least you have a system user with sudo privileges which you can use to connect to your server. Once you connect to your server via SSH run the following command to check if the MySQL database server is installed and which version it is:

mysql -V

Depending on which version you have installed, the output should be similar to this:

mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper

If you need to connect to the MySQL database server through the command line as user root run the following command:

mysql -u root -p

MySQL will ask you to enter the password for the MySQL root user. In case you don’t have a password set up for the MySQL root user, you can connect with the following command instead:

mysql -u root

Of course, it is always recommended to keep your services secure, so if you haven’t set up the MySQL root password yet, you can do that now using the mysql_secure_installation command:

mysql_secure_installation

Then, follow the on-screen instructions to finish the setup:

Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: y

Please set the password for root here.
New password:

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

All done!

That’s it, you can now connect to the MySQL database server as the MySQL root user using your new password.

Import an SQL file into MySQL database

We will now show you how to import an SQL file into an existing MySQL database.

To list all existing databases in your MySQL database server, first connect to your database server with:

mysql -u root -p

and then run the following command:

mysql> show databases;

This will list all databases created in MySQL, giving you an output similar to this:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

If you want to create a new database for the SQL file, you can do it with the following command:

mysql> CREATE DATABASE DatabaseName;

To create a MySQL user and assign a new password to it, run the following command:

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

To give the new or existing user access to the new databases, run the following:

mysql> GRANT ALL ON DatabaseName.* TO 'DatabaseUser'@"localhost";

Lastly, reload all the privileges with:

mysql> FLUSH PRIVILEGES;

and exit the MySQL server with:

mysql> exit;

Your database is now ready, and we can now import the SQL file.

To import the SQL file, for example, BackupDatabase.sql, into your new database, simply run the following command:

mysql -u DatabaseUser -p DatabaseName < BackupDatabase.sql

You will be asked for the password of the database user to which the database is assigned. Enter your database user password to finish the import.

With this, the BackupDatabase.sql file has been successfully imported.

Export a MySQL database into SQLfile

Additionally, we will also show you how to easily export an existing MySQL database into a SQL file. This can be done with the mysqldump command. For example:

mysqldump -u DatabaseUser -p DatabaseName > BackupDatabase.sql

Again, you will be asked for the database user password, and it will create an SQL file of your database which you can store it as a backup, and import it later if needed.

Of course, you don’t have to import your SQL files in MySQL,  if you are using one of our outsourced Linux server management services, in which case you can simply ask our expert Linux admins to help you. They are available 24×7 and will take care of your request immediately.

PS. If you liked this post on how to Import an SQL file into MySQL Database, please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks

The post How to Import an SQL File Into MySQL Database appeared first on LinuxHostSupport.

Check the size of all MySQL databases

To check the size of MySQL databases and tables we can use the virtual database called information_schema which provides access to database metadata, including the size of the databases and tables.

Enter the MySQL shell as user root by executing the following command:

mysql -u root -p

To print the size of all your databases run the following command:

SELECT 
    table_schema "Database Name",
    ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS "Database Size in MB"
FROM 
    information_schema.TABLES
GROUP BY table_schema ; 

The output should be similar to the following:

+-----------------------+---------------------+
| Database Name         | Database Size in MB |
+-----------------------+---------------------+
| admin_110             |              251.28 |
| admin_120             |               18.99 |
| admin_25              |               11.86 |
| admin_562             |               28.45 |
| admin_877             |                4.20 |
| information_schema    |                0.14 |
| mysql                 |                0.69 |
| performance_schema    |                0.00 |
| user_174              |               23.72 |
| user_379              |                0.26 |
| user_604              |                0.64 |
| user_625              |                1.22 |
| user_820              |                0.73 |
| user_854              |                2.25 |
| user_901              |                1.10 |
| user_961              |                1.60 |
+-----------------------+---------------------+

Check the size of all Tables in a specific MySQL database

To print the size of all tables in a specific MySQL database, same as before we will use the information from the virtual database information_schema:

SELECT 
    table_name as "Table Name",
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Table Size in MB"
FROM 
    information_schema.TABLES 
where 
    table_schema = 'admin_562';

The output should be similar to the following:

+-----------------------+------------------+
| Table Name            | Table Size in MB |
+-----------------------+------------------+
| wp_commentmeta        |             0.05 |
| wp_comments           |            12.42 |
| wp_links              |             0.03 |
| wp_options            |             1.98 |
| wp_postmeta           |             0.19 |
| wp_posts              |             1.75 |
| wp_term_relationships |             0.06 |
| wp_term_taxonomy      |             0.05 |
| wp_termmeta           |             0.05 |
| wp_terms              |             0.05 |
| wp_usermeta           |             0.05 |
| wp_users              |             0.06 |
| wp_wfBadLeechers      |             0.02 |
| wp_wfBlockedIPLog     |             0.06 |
| wp_wfBlocks           |             0.03 |
| wp_wfBlocksAdv        |             0.02 |
| wp_wfConfig           |             3.28 |
| wp_wfCrawlers         |             0.02 |
| wp_wfFileMods         |             1.52 |
| wp_wfHits             |             2.20 |
| wp_wfHoover           |             0.03 |
| wp_wfIssues           |             2.52 |
| wp_wfKnownFileList    |             0.20 |
| wp_wfLeechers         |             0.05 |
| wp_wfLockedOut        |             0.02 |
| wp_wfLocs             |             0.02 |
| wp_wfLogins           |             0.08 |
| wp_wfNet404s          |             0.03 |
| wp_wfNotifications    |             0.02 |
| wp_wfPendingIssues    |             0.31 |
| wp_wfReverseCache     |             0.02 |
| wp_wfSNIPCache        |             0.06 |
| wp_wfScanners         |             0.02 |
| wp_wfStatus           |             1.17 |
| wp_wfThrottleLog      |             0.03 |
| wp_wfVulnScanners     |             0.02 |
+-----------------------+------------------+

Check the size of a specific Table in a specific MySQL database

In the last example we will show you how to check the of a specific table size by the database name and the table name, also by querying the virtual database information_schema :

SELECT 
    table_name as "Table Name",
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Table Size in MB"
FROM 
    information_schema.TABLES 
where 
   table_schema = 'admin_877' and table_name = 'wp_wfConfig';

The output should be similar to the following:

+-------------+------------------+
| Table Name  | Table Size in MB |
+-------------+------------------+
| wp_wfConfig |             3.28 |
+-------------+------------------+

If you use one of our Outsourced Linux Server Support Services, you can simply ask our expert Linux admins to check the size of your MySQL databases and tables for

you. They are available 24×7 and will take care of your request immediately.

PS. If you liked this post, on how to check the size of MySQL databases and tables, please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.

The post How to Check MySQL Database Size appeared first on LinuxHostSupport.