Learn how to solve the MySQL/MariaDB ‘Too many connections’ error, which occurs when all available MySQL/MariaDB connections are in use. This error may occur in a script which connects to MySQL/MariaDB, or on a webpage which is generated (in whole or in part) from elements provided by a MySQL/MariaDB database. Read on for more.
MySQL/MariaDB are used to create databases. In Linux, installation takes just a few steps in the terminal.
Cheap domain names
Domains as original as your ideas.
What are you waiting for? Make your project a success with the perfect domain extension!
- A Cloud Server running Linux (CentOS 7 or Ubuntu 16.04)
For any Cloud Server with Plesk, databases should always be installed and managed through the Plesk interface.
MySQL vs. MariaDB
MySQL was first developed in 1995 and acquired by Sun Microsystems in 2008, and then again by Oracle in 2010. MariaDB was developed as a fork of the MySQL project in 2009, due to concerns about Oracle’s proprietary requirements. Although MySQL’s source code is publicly available under the terms of the GNU General Public License, MariaDB is a fully open source project.
MariaDB was developed as a ‘drop-in’ replacement for MySQL. As such, both software packages are functionally equivalent and interchangeable.
MySQL is the default on Ubuntu systems, while MariaDB is the default on CentOS systems. Therefore, this guide will cover installing and updating MySQL on Ubuntu 16.04 and MariaDB on CentOS 7.
Install MySQL on Ubuntu 16.04
MySQL is usually installed by default on a standard Cloud Server running Ubuntu 16.04. Use the sudo mysql --version command to verify that MySQL is already installed:
user@localhost:~# sudo mysql --version mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper
If MySQL is not installed, you can install it by first updating your packages:
sudo apt-get update
Then install MySQL:
sudo apt-get install mysql-server
Follow the prompts to install MySQL.
MySQL should start itself automatically when installed. If it does not start, you can start it with the following command:
sudo systemctl start mysql
Enable MySQL to start at boot with the command:
sudo systemctl enable mysql
If you need to stop or restart MySQL, use the commands:
sudo systemctl stop mysql sudo systemctl restart mysql
vServer (VPS) from IONOS
Low-cost, powerful VPS hosting for running your custom applications, with a personal assistant and 24/7 support.
Install MariaDB on CentOS 7
In contrast to MySQL, MariaDB is usually installed by default on a standard Cloud Server running CentOS 7. Use the sudo mysql –version command to verify that MariaDB is installed:
[user@localhost ~]# sudo mysql --version mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1
If MariaDB is not installed, you can install the database management system by first updating your system:
sudo yum update
Then install MariaDB:
sudo yum install mariadb-server
MariaDB should start itself automatically when installed. If it does not start, you can start it with the command:
sudo systemctl start mariadb
Enable MariaDB to start at boot with the command:
sudo systemctl enable mariadb
If you need to stop or restart MariaDB, use the following commands:
sudo systemctl stop mariadb sudo systemctl restart mariadb
Log in to the MySQL/MariaDB client
From the command line, enter the following command to register with the MySQL/MariaDB client:
mysql -u root -p
For a default MySQL/MariaDB installation, use the default root password which was set when the server was created. If you installed MySQL/MariaDB, enter the password which you set for the root user during the installation process.
After entering the password, you will be taken to the MySQL/MariaDB client prompt.
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 83 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Create, select, and drop a database
Create a Database
Use CREATE DATABASE[database name]; to create a database. For example, to create a database named testdb the command is:
CREATE DATABASE testdb;
List and Select a Database
Use SHOW DATABASES; to list all available databases:
MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | testdb | +--------------------+ 4 rows in set (0.00 sec)
Use USE[database name]; to connect to a database and select it for use:
MariaDB [(none)]> USE testdb; Database changed
Drop a Database
If you want to delete a database, use DROP DATABASE[database name]. For example, to delete the testdb database, enter the following command in the terminal:
DROP DATABASE testdb;
To exit the client, type:
Hit Enter to confirm exiting the client.
Cloud backup from IONOS
Make costly downtime a thing of the past and back up your business the easy way!
Create and drop a table
Use CREATE TABLE[table name] (column definitions); to create a table. A full list of CREATE TABLE parameters can be found in the CREATE TABLE chapter of the official MySQL reference manual.
For example, to create a table testtable with two basic columns, the command is:
CREATE TABLE testtable ( id char(5) PRIMARY KEY, name varchar(40) );
Use SHOW TABLES; to verify that your table was created:
MariaDB [testdb]> SHOW TABLES; +------------------+ | Tables_in_testdb | +------------------+ | testtable | +------------------+ 1 row in set (0.00 sec)
Drop a Table
Use DROP TABLE[table name]; to delete a table. For example, to delete the testtable table, the command is:
DROP TABLE testtable;
Use SHOW TABLES; to verify that your table was deleted:
MariaDB [testdb]> SHOW TABLES; Empty set (0.00 sec)
Working with records: insert, select, and update data
Insert Data into a Table
Use INSERT INTO[table name] VALUES (data, data...); to insert data into a table. A full list of INSERT parameters can be found in the ‘INSERT Syntax’ chapter of the official MySQL Reference Manual.
For example, to insert a record into the table testable the command is:
INSERT INTO testable VALUES (1, 'Alice'); INSERT INTO testable VALUES (2, 'Bob');
It is important to list the values in the same order as the columns of the table. In our example, the table’s first column is id and the second column is name. Therefore, we need to insert the ID as the first value, and the name as the second.
Select Table Data
Use SELECT to select data from a table and generate different queries for a database. A full list of SELECT parameters can be found in the ‘SELECT Syntax’ chapter of the official MySQL Reference Manual.
For example, to list all of the contents of our testtable the command is:
SELECT * from testtable;
This will return all of the table contents.
MariaDB [testdb]> SELECT * from testtable; +----+-------+ | id | name | +----+-------+ | 1 | Alice | | 2 | Bob | +----+-------+ 2 rows in set (0.00 sec)
You can also filter out which columns you want to select. For example, use SELECT name FROM testtable; to see only the name field for all records:
MariaDB [testdb]> SELECT name FROM testtable; +-------+ | name | +-------+ | Alice | | Bob | +-------+ 2 rows in set (0.00 sec)
Update a Record
Use UPDATE[table name] SET [new values] WHERE [matching condition] to update a record. A full list of UPDATE parameters can be found in the ‘UPDATE Syntax’ chapter of the official MySQL Reference Manual.
For example, to change the record with ID of 2 from Bob to Carl the command is:
UPDATE testtable SET name = 'Carl' WHERE id = '2';
Use SELECT to verify that the record was updated correctly:
MariaDB [testdb]> SELECT * FROM testtable; +----+-------+ | id | name | +----+-------+ | 1 | Alice | | 2 | Carl | +----+-------+ 2 rows in set (0.00 sec)