Install with APT¶
Use the Percona repositories to install using APT.
Prerequisits¶
-
Either use
sudo
or run as root -
Stable Internet access
Installation steps¶
The examles of the “expected output” depend on the operating system. The following examples are based on Ubuntu 22.04.
-
Update the package index.
$ sudo apt update
The result depends on the operating system. The following result is based on Ubuntu 22.04:
Expected output
```{.text .no-copy} Hit:1 http://us.archive.ubuntu.com/ubuntu jammy InRelease Get:2 http://us.archive.ubuntu.com/ubuntu jammy-updates InRelease [119 kB] Hit:3 http://us.archive.ubuntu.com/ubuntu jammy-backports InRelease ... ```
-
Install curl.
$ sudo apt install -y curl
The result depends on the operating system. The following result is based on Ubuntu 22.04:
Expected output
Reading package lists... Done Building dependency tree... Done Reading state information... Done ...
-
Download the
percona-release
repository package:$ curl -O https://repo.percona.com/apt/percona-release_latest.generic_all.deb
You should see the following result:
Expected output
% Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 11804 100 11804 0 0 17375 0 --:--:-- --:--:-- --:--:-- 17358 ...
-
Install the downloaded package and any dependencies:
$ sudo apt install -y gnupg2 lsb-release ./percona-release_latest.generic_all.deb
You should see the following result:
Expected output
Reading package lists... Done Building dependency tree... Done Reading state information... Done Note, selecting 'percona-release' instead of './percona-release_latest.generic_all.deb' lsb-release is already the newest version (11.1.0ubuntu4). lsb-release set to manually installed. ...
-
Update the package listing.
$ sudo apt update
The result depends on the operating system. The following result is based on Ubuntu 22.04:
Expected output
Hit:1 http://us.archive.ubuntu.com/ubuntu jammy InRelease Get:2 http://repo.percona.com/percona/apt jammy InRelease [15.7 kB] Hit:3 http://us.archive.ubuntu.com/ubuntu jammy-updates InRelease Hit:4 http://us.archive.ubuntu.com/ubuntu jammy-backports InRelease ...
-
Set up the Percona Server for MySQL 8.0 repository:
$ sudo percona-release setup ps80
The result depends on the operating system. The following result is based on Ubuntu 22.04:
Expected output
* Disabling all Percona Repositories * Enabling the Percona Server 8.0 repository * Enabling the Percona Tools repository Hit:1 http://us.archive.ubuntu.com/ubuntu jammy InRelease Hit:2 http://us.archive.ubuntu.com/ubuntu jammy-updates InRelease Hit:3 http://repo.percona.com/prel/apt jammy InRelease ...
-
Enable the Percona Server for MySQL release.
$ sudo percona-release enable ps-80 release
You should see the following result:
Expected output
* Enabling the Percona Server 8.0 repository <*> All done! ==> Please run "apt-get update" to apply changes
-
Update the package listing.
$ sudo apt update
The result depends on the operating system. The following result is based on Ubuntu 22.04:
Expected output
Hit:1 http://us.archive.ubuntu.com/ubuntu jammy InRelease Get:2 http://repo.percona.com/percona/apt jammy InRelease [15.7 kB] Hit:3 http://us.archive.ubuntu.com/ubuntu jammy-updates InRelease Hit:4 http://us.archive.ubuntu.com/ubuntu jammy-backports InRelease ...
-
Install Percona Server for MySQL 8.0.
$ sudo apt install -y percona-server-server
The result depends on the operating system. The following result is based on Ubuntu 22.04:
Expected output
Reading package lists... Done Building dependency tree... Done Reading state information... Done The following additional packages will be installed: debsums libaio1 libdpkg-perl libfile-fcntllock-perl libfile-fnmatch-perl libmecab2 percona-server-client percona-server-common Suggested packages: debian-keyring gcc | c-compiler binutils git bzr The following NEW packages will be installed: debsums libaio1 libdpkg-perl libfile-fcntllock-perl libfile-fnmatch-perl libmecab2 percona-server-client percona-server-common percona-server-server 0 upgraded, 9 newly installed, 0 to remove and 161 not upgraded. Need to get 172 MB of archives. After this operation, 612 MB of additional disk space will be used. ...
-
The installation asks you to enter a password. We use ‘secret’ for these examples, but you can use any password, but you must remember to use your password for the rest of the Quickstart.
-
Confirm your password.
-
Choose the type of authentication, based on the compatibility and the security requirements of your applications.
The Strong password encryption uses a more secure hashing algorithm to store and verify passwords, which makes it harder for attackers to crack them.
The Legacy authentication method uses the older and less secure hashing algorithm that was used in previous versions of MySQL.
-
[Optional] You can increase the security of MySQL by running
sudo mysql_secure installation
.After installing MySQL, you should run the
mysql_secure_installation
script to improve the security of your database server. This script helps you perform several important tasks, such as:-
Set a password for the root user
-
Select a level for the password validation policy
-
Remove anonymous users
-
Disable root login remotely
-
Remove the test database
-
Reload the privilege table to ensure all changes take effect immediately
By running this script, you can prevent unauthorized access to your server and protect your data from potential threats.
$ sudo mysql_secure_installation
Expected output
Securing the MySQL server deployment. Enter password for user root: VALIDATE PASSWORD COMPONENT can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD component? Press y|Y for Yes, any other key for No: There are three levels of password validation policy: LOW Length >= 8 MEDIUM Length >= 8, numeric, mixed case, and special characters STRONG Length >= 8, numeric, mixed case, special characters and dictionary file Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1 Using existing password for root. Estimated strength of the password: 0 Change the password for root ? ((Press y|Y for Yes, any other key for No) : New password: Re-enter new password: Estimated strength of the password: 100 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? (Press y|Y for Yes, any other key for No) : Success. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Success. By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : - Dropping test database... Success. - Removing privileges on test database... Success. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Success. All done!
-
-
When the installation completes, check the service status.
$ sudo systemctl status mysql
Expected output
● mysql.service - Percona Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: active (running) since Fri 2024-02-16 10:24:58 UTC; 3min 7s ago Process: 4456 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS) Main PID: 4501 (mysqld) Status: "Server is operational" Tasks: 39 (limit: 2219) Memory: 371.7M CPU: 11.800s CGroup: /system.slice/mysql.service └─4501 /usr/sbin/mysqld Feb 16 10:24:56 vagrant systemd[1]: Starting Percona Server... Feb 16 10:24:58 vagrant systemd[1]: Started Percona Server.
If needed, restart the service
$ sudo systemctl status mysql
-
Log in to the server. Use the password that you entered during the installation process, which could be
secret
orwhatever password you have selected. You do not see the characters in the password as you type.$ mysql -uroot -p Enter password:
Expected output
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.35-27 Percona Server (GPL), Release '27', Revision '2f8eeab2'$ Copyright (c) 2009-2023 Percona LLC and/or its affiliates Copyright (c) 2000, 2023, 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>
Create a database¶
Benefits and what to watch out for when creating databases and tables
Creating a database and table has the following benefits:
- Store and organize your data in a structured and consistent way.
- Query and manipulate your data using SQL.
- Enforce data integrity and security using constraints, triggers, views, roles, and permissions.
- Optimize your data access and performance using indexes, partitions, caching, and other techniques.
When you create a table, design your database schema carefully, changing it later may be difficult and costly. You should experiment with concurrency, transactions, locking, isolation, and other issues that may arise when multiple users access the same data. You must backup and restore your data regularly, as data loss or corruption may occur due to hardware failures, human errors, or malicious attacks.
To create a database, use the CREATE DATABASE
statement. You can optionally specify the character set and collation for the database in the statement. After the database is created, select the database using the USE
statement or the -D
option in the MySQL client.
mysql> CREATE DATABASE mydb;
Expected output
Query OK, 1 row affected (0.01 sec)
mysql> use mydb;
Expected output
Database changed
Create a table¶
Create a table using the CREATE TABLE
statement. You can specify the values for each column or use the DEFAULT keyword for columns with default values, data types, constraints, indexes, and other options.
mysql> CREATE TABLE `employees` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(255) default NULL,
`email` varchar(255) default NULL,
`country` varchar(100) default NULL,
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;
Expected output
Query OK, 0 rows affected, 1 warning (0.03 sec)
Insert data into the table¶
Insert data into the table using the INSERT INTO
SQL statement. This statement adds multiple records into a table in one statement.
mysql> INSERT INTO `employees` (`name`,`email`,`country`)
VALUES
("Erasmus Richardson","posuere.cubilia.curae@outlook.net","England"),
("Jenna French","rhoncus.donec@hotmail.couk","Canada"),
("Alfred Dejesus","interdum@aol.org","Austria"),
("Hamilton Puckett","dapibus.quam@outlook.com","Canada"),
("Michal Brzezinski","magna@icloud.pl","Poland"),
("Zofia Lis","zofial00@hotmail.pl","Poland"),
("Aisha Yakubu","ayakubu80@outlook.com","Nigeria"),
("Miguel Cardenas","euismod@yahoo.com","Peru"),
("Luke Jansen","nibh@hotmail.edu","Netherlands"),
("Roger Pettersen","nunc@protonmail.no","Norway");
Expected output
Query OK, 10 rows affected (0.02 sec)
Records: 10 Duplicates: 0 Warnings: 0
Run a SELECT query¶
SELECT queries retrieve data from one or more tables based on specified criteria. They are the most common type of query and can be used for various purposes, such as displaying, filtering, sorting, aggregating, or joining data. SELECT queries do not modify the data in the database but can affect the performance if the query involves large or complex datasets.
mysql>SELECT id, name, email, country FROM employees WHERE country = 'Poland';
Expected output
+----+-------------------+---------------------+---------+
| id | name | email | country |
+----+-------------------+---------------------+---------+
| 5 | Michal Brzezinski | magna@icloud.pl | Poland |
| 6 | Zofia Lis | zofial00@hotmail.pl | Poland |
+----+-------------------+---------------------+---------+
2 rows in set (0.00 sec)
Run an Update query¶
UPDATE queries modify existing data in a table. They are used to change or correct the information stored in the database. UPDATE queries can update one or more columns and rows simultaneously, depending on the specified conditions. They may also fail if they violate any constraints or rules defined on the table.
An example of an UPDATE query and then run a [SELECT](#select-query) with a WHERE clause to verify the update.
mysql> UPDATE employees SET name = 'Zofia Niemec' WHERE id = 6;
Expected output
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT name FROM employees WHERE id = 6;
Expected output
+--------------+
| name |
+--------------+
| Zofia Niemec |
+--------------+
1 row in set (0.00 sec)
Run an INSERT query¶
INSERT queries add new data to a table. They are used to populate the database with new information. INSERT queries can insert one or more rows at a time, depending on the syntax. The query may fail if it violates any constraints or rules defined on the table, such as primary keys, foreign keys, unique indexes, or triggers.
Insert a row into a table and then run a SELECT with a WHERE clause to verify the record was inserted.
mysql> INSERT INTO `employees` (`name`,`email`,`country`)
VALUES
("Kenzo Sasaki","KenSasaki@outlook.com","Japan");
Expected output
Query OK, 1 row affected (0.01 sec)
mysql> SELECT id, name, email, country FROM employees WHERE id = 11;
Expected output
+----+--------------+-----------------------+---------+
| id | name | email | country |
+----+--------------+-----------------------+---------+
| 11 | Kenzo Sasaki | KenSasaki@outlook.com | Japan |
+----+--------------+-----------------------+---------+
1 row in set (0.00 sec)
Run a Delete query¶
DELETE queries remove existing data from a table. They are used to clean up the information no longer needed or relevant in the database. The DELETE queries can delete one or more rows at a time, depending on the specified conditions. They may also trigger cascading deletes on related tables if foreign key constraints are enforced.
Delete a row in the table and run a SELECT with a WHERE clause to verify the deletion.
mysql> DELETE FROM employees WHERE id >= 11;
Expected output
Query OK, 1 row affected (0.01 sec)
mysql> SELECT id, name, email, country FROM employees WHERE id > 10;
Expected output
Empty set (0.00 sec)
Troubleshooting¶
-
Connection Issues: Double-check credentials, ensure service is running, and verify firewall configurations if applicable.
-
Permission Errors: Grant necessary permissions to users using GRANT statements within the MySQL shell.
-
Package Installation Issues: Refer to Percona documentation and online forums for specific error messages and solutions.
Security best practices¶
-
Strong Passwords: Utilize complex and unique passwords for all users, especially the root account.
-
Minimize Permissions: Grant users only the privileges necessary for their tasks.
-
Disable Unnecessary Accounts: Remove test accounts and unused accounts.
-
Regular Backups: Implement consistent backup routines to safeguard your data.
-
Keep Software Updated: Maintain Percona Server and related packages updated with security patches.
-
Monitor Server Activity: Employ tools, like Percona Monitoring and Management, and logs to monitor server activity for suspicious behavior.
Next step¶
Get expert help¶
If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services.