Step-by-Step Guide to Setting Up MySQL on VPS

What you will read?

1 Prerequisites and Preparing the VPS
2 Installing MySQL from Official Repositories
3 Initial MySQL Setup
4 Securing MySQL (MySQL Secure Installation)
5 Creating and Managing MySQL Users
6 Creating Databases in MySQL
7 Configuring MySQL for Performance Optimization
8 Connecting to VPS via SSH
9 Updating the VPS Operating System
10 Backups and Restoring in MySQL

MySQL is a database system used to store and manage data. It is popular because it is free to use and is designed to be fast, reliable, and easy to set up. Many developers and businesses choose MySQL for their projects, especially for websites and web applications. It works by using a language called SQL to interact with the data.

It is widely used in various fields, such as e-commerce, content management systems, and more. MySQL is known for its ability to handle large amounts of data and to perform tasks quickly.

Prerequisites and Preparing the VPS

Before installing MySQL on your VPS (Virtual Private Server), there are a few important steps to take to ensure the system is ready. Here’s what you need to do:

Choose an Operating System:

MySQL can be installed on different operating systems. Common choices for VPS are Linux-based systems like Ubuntu, CentOS, and Debian. Make sure your VPS is running a supported operating system.

Ensure You Have Root or Sudo Access:

To install software on your VPS, you’ll need root access or a user account with sudo privileges. If you don’t have the necessary permissions, you will need to contact the administrator.

Update Your System:

Before installing anything, it’s important to update the system to make sure it has the latest security patches and software updates. You can do this using the following commands:

For Ubuntu/Debian:

sudo apt update && sudo apt upgrade -y

For CentOS:

sudo yum update -y

Install Necessary Dependencies:
Some software packages might be required for the installation. For MySQL, you might need to install dependencies like wget or curl. You can install them using:

For Ubuntu/Debian:

sudo apt install wget curl -y

For CentOS:

sudo yum install wget curl -y

Installing MySQL from Official Repositories

Now that your VPS is ready and up to date, you can proceed with installing MySQL. The easiest way to install MySQL is through the official repositories of your operating system. Below are the steps for different Linux distributions:

For Ubuntu/Debian-based systems:

Install MySQL Server:
To install MySQL, run the following command:

sudo apt install mysql-server -y

Start MySQL Service:
After installation, the MySQL service should start automatically. To ensure it is running, you can check the status of the MySQL service:

sudo systemctl status mysql

Enable MySQL to Start on Boot:
To make sure MySQL starts automatically when the server reboots, use the following command:

sudo systemctl enable mysql

For CentOS/RHEL-based systems:

install MySQL Repository:
MySQL is not included in the default CentOS or RHEL repositories, so you need to add the MySQL official repository first. Run the following command:

sudo yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

Install MySQL Server:
Once the repository is added, you can install MySQL by running:

sudo yum install mysql-server -y

Start MySQL Service:
After installation, start the MySQL service with the following command:

sudo systemctl start mysqld

Enable MySQL to Start on Boot:
Enable MySQL to start automatically on boot:

sudo systemctl enable mysqld

Check MySQL Status:
Ensure MySQL is running by checking its status:

sudo systemctl status mysqld

Initial MySQL Setup

After installing MySQL on your VPS, you need to perform some basic setup to ensure that MySQL is secure and ready for use. Below are the steps for the initial MySQL setup:

1. Run the MySQL Secure Installation Script:

MySQL provides a security script that helps to improve the security of your installation by setting a root password, removing insecure default settings, and more. To start this process, run the following command:

sudo mysql_secure_installation

You will be prompted to answer a few questions:

Set a root password: If you haven’t already set a root password, the script will ask you to create one.
Remove anonymous users: This option helps remove any anonymous users that might be created by default.
Disallow root login remotely: For security purposes, it’s recommended to disable remote root access.
Remove test database: MySQL installs a test database by default, which you can remove for added security.
Reload privilege tables: This applies all changes made during the setup.

Once you’ve answered the questions, the MySQL installation will be secured.

2. Login to MySQL:

After securing your installation, you can log in to MySQL as the root user. Use the following command:

sudo mysql -u root -p

When prompted, enter the root password you set earlier.

3. Verify MySQL Installation:

Once logged in, you can verify the installation by running the following SQL command to check the MySQL version:

SELECT VERSION();

You should see the version of MySQL you installed.

4. Create a New MySQL User (Optional):

It’s recommended to create a separate user for managing databases instead of using the root account. To create a new user, run the following commands within the MySQL prompt:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Replace 'newuser' and 'password' with the desired username and password.

5. Exit MySQL:

After completing your initial setup, you can exit the MySQL shell by typing:

EXIT;

Once you’ve completed the initial setup, MySQL will be secured and ready for use on your VPS!

Securing MySQL (MySQL Secure Installation)

The mysql_secure_installation script is a built-in utility in MySQL that helps you secure your MySQL installation by performing a series of tasks. These tasks include setting the root password, removing insecure default settings, and improving the overall security of your database server. Here’s how to run and configure it:

Run the MySQL Secure Installation Script:

To start the process, you can run the following command in the terminal:

sudo mysql_secure_installation

This will start the interactive setup process. The script will ask several questions, and your answers will help secure your MySQL server.

Steps and Options During the Setup:

Set a Root Password:
The first prompt will ask if you want to configure the root password. If you haven’t already set one, it will prompt you to do so. Choose a strong, secure password to prevent unauthorized access.
Remove Anonymous Users:
The script will ask if you want to remove anonymous users. By default, MySQL might create anonymous users that can pose a security risk. It is highly recommended to remove these users, so answer “Y” (yes).
Disallow Remote Root Login:
The next question will ask if you want to disable remote root login. For security reasons, it is best to disable remote root access and only allow root login from localhost. Answer “Y” to disable remote root login.
Remove Test Database:
MySQL installs a default test database which can be a security concern. The script will ask if you want to remove this test database. It is recommended to remove it by answering “Y.”
Reload Privilege Tables:
Finally, the script will ask if you want to reload the privilege tables to apply all the changes. Answer “Y” to apply these changes.

Confirmation:

Once you’ve completed the interactive setup, MySQL will be secured and configured for improved security. The root password will be set, anonymous users will be removed, and other settings will be tightened to reduce the attack surface.

Re-run the Script (Optional):

If you missed any options or want to adjust settings later, you can re-run the mysql_secure_installation script at any time.

Creating and Managing MySQL Users

In MySQL, it is best practice to create separate user accounts for different tasks, rather than using the root account for everything. This enhances security and makes it easier to manage permissions. Below are the steps to create and manage MySQL users.

Login to MySQL:

First, log in to MySQL as the root user:

sudo mysql -u root -p

Creating a New MySQL User:

To create a new MySQL user, use the CREATE USER command. Here’s the basic syntax:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Replace 'username' with the desired username.
Replace 'host' with the host from which the user will be connecting. For local access, use 'localhost'. For remote access, use '%' (wildcard) or specify an IP address.
Replace 'password' with a strong password for the user.

Example:

CREATE USER 'john'@'localhost' IDENTIFIED BY 'strongpassword';

Granting Privileges to the User:

After creating a user, you need to grant privileges to allow them to perform specific actions on databases. Use the GRANT command for this.

To grant full privileges on a specific database:
```
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
```
Example (granting all privileges on the test_db database to the john user):
```
GRANT ALL PRIVILEGES ON test_db.* TO 'john'@'localhost';
```

To grant privileges on all databases:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'host' WITH GRANT OPTION;

Example:

GRANT ALL PRIVILEGES ON *.* TO 'john'@'localhost' WITH GRANT OPTION;

To grant specific privileges (e.g., SELECT, INSERT, UPDATE):

GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'host';

Flushing Privileges:

After granting or modifying privileges, it’s important to reload the privileges to apply the changes. Run the following command:

FLUSH PRIVILEGES;

Checking User Privileges:

To check the privileges of a user, you can use the SHOW GRANTS command:

SHOW GRANTS FOR 'username'@'host';

Example:

SHOW GRANTS FOR 'john'@'localhost';

Modifying User Privileges:

If you need to modify the privileges of an existing user, you can use the GRANT command again to add new privileges or remove unwanted ones.

To revoke a privilege:

REVOKE privilege_type ON database_name.* FROM 'username'@'host';

Example (removing UPDATE privilege from john):

REVOKE UPDATE ON test_db.* FROM 'john'@'localhost';

After revoking privileges, remember to flush them:

FLUSH PRIVILEGES;

Deleting a User:

If you need to delete a user, use the DROP USER command:

DROP USER 'username'@'host';

Example:

DROP USER 'john'@'localhost';

This will remove the user and revoke all associated privileges.

8. Exit MySQL:

After finishing user management tasks, you can exit the MySQL shell:

EXIT;

By following these steps, you can effectively manage MySQL users and control access to your databases.

Creating Databases in MySQL

As mentioned before, after logging in to MySQL, you can create and manage databases easily. Below are the steps for creating and managing databases without repeating the basic login process:

Creating a New Database:

To create a new database, use the CREATE DATABASE command:

CREATE DATABASE my_database;

Selecting the Database:

After creating the database, select it for use:

USE my_database;

Listing All Databases:

To see all databases:

SHOW DATABASES;

Dropping (Deleting) a Database:

To permanently remove a database:

DROP DATABASE my_database;

Checking the Database:

Once inside the database, you can check the tables:

SHOW TABLES;

This approach focuses on the actual database management commands without repeating login steps.

Configuring MySQL for Performance Optimization

Optimizing MySQL performance involves tuning various settings related to the server, database, and queries. Below are the key steps for configuring MySQL for better performance.

1. Review and Adjust MySQL Configuration File:

The MySQL configuration file (usually my.cnf or my.ini) contains many settings that affect performance. To find the configuration file, run:

mysql --help | grep 'my.cnf'

ommon locations for this file are:

/etc/my.cnf
/etc/mysql/my.cnf
/etc/mysql/mysql.conf.d/mysqld.cnf

Edit the file to adjust performance settings:

sudo nano /etc/mysql/my.cnf

2. Optimize Buffer Sizes:

Buffers are important for performance because they control the memory allocation for various operations. Key buffer settings include:

innodb_buffer_pool_size: Determines how much memory is allocated for caching InnoDB data and indexes.
innodb_buffer_pool_size = 2G
(Set this value to 70-80% of your system’s RAM for best performance.)
key_buffer_size: Used for MyISAM indexes. If you’re using MyISAM tables, increase this setting.
```
key_buffer_size = 512M
```

Adjust Query Cache Settings:

If you’re running MySQL with a read-heavy workload, enabling the query cache can speed up query execution by caching the results of SELECT statements.

query_cache_size: Allocates memory to store query results.
```
query_cache_size = 128M
```
query_cache_limit: Determines the maximum size of a query result that can be cached.
```
query_cache_limit = 2M
```

To enable query cache:

query_cache_type = 1

4. Optimize InnoDB Settings:

InnoDB is the default storage engine for MySQL, and there are several settings you can tweak to improve its performance:

innodb_flush_log_at_trx_commit: Controls the behavior of InnoDB logs. Setting it to 2 will improve performance at the cost of durability.
```
innodb_flush_log_at_trx_commit = 2
```
innodb_log_buffer_size: The buffer used for logging. Increase this size for better performance if you have a write-heavy workload.
```
innodb_log_buffer_size = 64M
```

5. Tuning Connection Settings:

Optimize the number of connections MySQL can handle and the time to wait for a connection:

max_connections: The maximum number of simultaneous connections allowed.
```
max_connections = 200
```
wait_timeout: The time MySQL waits for a client to send data before closing the connection.
```
wait_timeout = 600
```

6. Enable Slow Query Log:

Enabling the slow query log helps identify inefficient queries that take a long time to execute. This is useful for troubleshooting performance issues.

slow_query_log: Enable slow query logging.
```
slow_query_log = 1
```
long_query_time: Sets the threshold for query execution time. Queries that exceed this time will be logged.
```
long_query_time = 2
```

7. Configure MySQL for High Concurrency:

MySQL can be configured to handle more concurrent connections and transactions:

thread_cache_size: Determines how many threads MySQL should keep in memory for reuse.
```
thread_cache_size = 50
```
table_open_cache: Controls how many open tables MySQL will keep cached.
```
table_open_cache = 400
```

8. Optimize Disk I/O:

If your MySQL server is disk I/O-bound, you can improve performance by optimizing disk settings:

innodb_io_capacity: Determines how many I/O operations InnoDB can perform per second.
```
innodb_io_capacity = 200
```

9. Restart MySQL Service:

After making these changes, restart MySQL to apply the configuration updates:

sudo systemctl restart mysql

10. Monitor MySQL Performance:

Regular monitoring is crucial to ensure your optimizations are effective. Use tools like mysqltuner or Percona Toolkit for continuous performance analysis.

Connecting to VPS via SSH

To manage your VPS and install MySQL, you need to connect to your server remotely. The most common way to do this is by using SSH (Secure Shell), which provides a secure channel to access your server’s command line. Here are the steps to connect to your VPS via SSH:

Find Your VPS IP Address:

When you set up your VPS, you will be provided with an IP address. This address is needed to connect to the server.

Get Your SSH Login Credentials:

You will need the username (usually root or a user with sudo privileges) and the password or SSH private key that was provided when you created your VPS.

Using SSH to Connect:

Open a terminal on your local machine and run the following command (replace your_vps_ip with your actual VPS IP address):

For Linux/Mac:

ssh root@your_vps_ip

For Windows:

If you’re using Windows, you can use an SSH client like PuTTY. Enter your VPS IP address and login credentials to connect.

Accept the SSH Key (First Time Connection):

The first time you connect to your VPS, you will be asked to accept the server’s SSH key. Type “yes” and press Enter to continue.

Enter Your Password or Use SSH Key:

If you’re using password authentication, you will be prompted to enter your password. If you’re using an SSH key, the key will be used automatically.

Once you’re successfully connected, you will have access to the command line of your VPS, and you can proceed with installing MySQL.

Updating the VPS Operating System

Before installing MySQL or any other software, it’s important to ensure that your VPS operating system is up to date. This ensures that you have the latest security patches, bug fixes, and the newest software versions. Here’s how you can update your VPS:

Connect to Your VPS:

First, ensure that you are connected to your VPS via SSH as we discussed in the previous step.

Update the Package List:

To get the latest information about available software updates, you need to update the package list. Run the following command:

For Ubuntu/Debian-based systems:
```
sudo apt update
```
For CentOS/RHEL-based systems:
```
sudo yum check-update
```

Upgrade Installed Packages:

After updating the package list, you should upgrade the installed packages to their latest versions. Run the following command:

For Ubuntu/Debian-based systems:
```
sudo apt upgrade -y
```
For CentOS/RHEL-based systems:
```
sudo yum update -y
```

Upgrade Distribution (Optional):

If you want to upgrade the entire operating system to a newer version (for example, from Ubuntu 18.04 to 20.04), you can do so with the following commands:

For Ubuntu/Debian-based systems:
```
sudo do-release-upgrade
```
For CentOS/RHEL-based systems, upgrading the distribution may require more specific steps.

Reboot the VPS (if necessary):

After upgrading the packages, you may need to reboot the VPS to apply some changes, especially if the kernel was updated. You can reboot with:

sudo reboot

Backups and Restoring in MySQL

Backing up your MySQL data is crucial for data safety and disaster recovery. MySQL provides several methods for backing up and restoring databases. Below are the most common methods:

1. Backing Up MySQL Databases:

Using `mysqldump` (for SQL-based backups):

The mysqldump utility is the most commonly used method for backing up MySQL databases. It creates a text file containing SQL statements that can recreate the database structure and data.

To backup a single database:

mysqldump -u root -p my_database > /path/to/backup/my_database_backup.sql

To backup all databases:

mysqldump -u root -p --all-databases > /path/to/backup/all_databases_backup.sql

To backup only the structure (without data):

mysqldump -u root -p -d my_database > /path/to/backup/my_database_structure.sql

To backup specific tables within a database:

mysqldump -u root -p my_database table1 table2 > /path/to/backup/my_database_tables_backup.sql

Using `mysqlpump` (alternative to `mysqldump`):

mysqlpump is a newer utility that can perform multi-threaded backups, which can be faster than mysqldump for large databases.

To backup a single database:

mysqlpump -u root -p my_database > /path/to/backup/my_database_backup.sql

To backup all databases:

mysqlpump -u root -p --all-databases > /path/to/backup/all_databases_backup.sql

2. Restoring MySQL Databases:

Using `mysql` Command (Restoring SQL-based backups):

To restore a database from a SQL backup file created with mysqldump, use the mysql command.

To restore a single database:

mysql -u root -p my_database < /path/to/backup/my_database_backup.sql

To restore all databases (if you backed up all databases):

mysql -u root -p < /path/to/backup/all_databases_backup.sql

Restoring with `mysqlpump`:

If you used mysqlpump for the backup, the restoration process is the same as using mysqldump:

mysqlpump -u root -p my_database < /path/to/backup/my_database_backup.sql

3. Automating Backups:

Using Cron Jobs (Linux-based systems):

You can automate regular backups using cron jobs on Linux. For example, to run a daily backup at midnight, add a cron job:

crontab -e

Then add the following line to schedule the backup:

0 0 * * * mysqldump -u root -p'your_password' my_database > /path/to/backup/my_database_$(date +\%F).sql

This will create a backup with a date-stamped filename each day.

Using Windows Task Scheduler (Windows-based systems):

On Windows, you can use Task Scheduler to schedule backups similarly. Create a batch script to run mysqldump and schedule it in Task Scheduler to run at specific times.

4. Backing Up and Restoring InnoDB Tablespaces:

For large databases with large tables, especially those using the InnoDB storage engine, you may want to back up the tablespaces directly for a more efficient backup. This can be done using xtrabackup or similar tools like Percona XtraBackup.

Backing up InnoDB with Percona XtraBackup:

innobackupex --user=root --password=your_password /path/to/backup/

Restoring InnoDB with Percona XtraBackup:

innobackupex --apply-log /path/to/backup/
innobackupex --copy-back /path/to/backup/

5. Testing Your Backup:

After performing a backup, always test the restoration process on a test server to ensure your backup is valid and can be restored correctly when needed. Regular backups and testing restore processes ensure data security and recovery in case of failure.

Step-by-Step Guide to Setting Up MySQL on VPS

Prerequisites and Preparing the VPS

Choose an Operating System:

Ensure You Have Root or Sudo Access:

Update Your System:

Installing MySQL from Official Repositories

For Ubuntu/Debian-based systems:

For CentOS/RHEL-based systems:

Initial MySQL Setup

1. Run the MySQL Secure Installation Script:

2. Login to MySQL:

3. Verify MySQL Installation:

4. Create a New MySQL User (Optional):

5. Exit MySQL:

Securing MySQL (MySQL Secure Installation)

Run the MySQL Secure Installation Script:

Steps and Options During the Setup:

Confirmation:

Re-run the Script (Optional):

Creating and Managing MySQL Users

Login to MySQL:

Creating a New MySQL User:

Granting Privileges to the User:

Flushing Privileges:

Checking User Privileges:

Modifying User Privileges:

Deleting a User:

8. Exit MySQL:

Creating Databases in MySQL

Creating a New Database:

Selecting the Database:

Listing All Databases:

Dropping (Deleting) a Database:

Checking the Database:

Configuring MySQL for Performance Optimization

1. Review and Adjust MySQL Configuration File:

2. Optimize Buffer Sizes:

Adjust Query Cache Settings:

4. Optimize InnoDB Settings:

5. Tuning Connection Settings:

6. Enable Slow Query Log:

7. Configure MySQL for High Concurrency:

8. Optimize Disk I/O:

9. Restart MySQL Service:

10. Monitor MySQL Performance:

Connecting to VPS via SSH

Find Your VPS IP Address:

Get Your SSH Login Credentials:

Using SSH to Connect:

For Linux/Mac:

For Windows:

Accept the SSH Key (First Time Connection):

Enter Your Password or Use SSH Key:

Updating the VPS Operating System

Connect to Your VPS:

Update the Package List:

Upgrade Installed Packages:

Upgrade Distribution (Optional):

Reboot the VPS (if necessary):

Backups and Restoring in MySQL

1. Backing Up MySQL Databases:

Using mysqldump (for SQL-based backups):

Using mysqlpump (alternative to mysqldump):

2. Restoring MySQL Databases:

Using mysql Command (Restoring SQL-based backups):

Restoring with mysqlpump:

3. Automating Backups:

Using Cron Jobs (Linux-based systems):

Using Windows Task Scheduler (Windows-based systems):

4. Backing Up and Restoring InnoDB Tablespaces:

5. Testing Your Backup:

Comments

Related Posts

Using `mysqldump` (for SQL-based backups):

Using `mysqlpump` (alternative to `mysqldump`):

Using `mysql` Command (Restoring SQL-based backups):

Restoring with `mysqlpump`: