Installing and Managing a MySQL Database on Your CentOS VPS

#### Prerequisites Before you begin, ensure you have: - A CentOS VPS (This guide is tested with CentOS 8) - A user account with sudo privileges - Basic familiarity with the command line #### Step 1: Update Your System Start by updating all packages to their latest versions: ```bash sudo dnf update -y ``` #### Step 2: Install MySQL CentOS 8 uses the `dnf` package manager. To install MySQL, first add the MySQL repository: ```bash sudo dnf install @mysql ``` Once the repository is added, install MySQL: ```bash sudo dnf install mysql-server -y ``` #### Step 3: Start and Enable MySQL Start the MySQL service and enable it to start on boot: ```bash sudo systemctl start mysqld sudo systemctl enable mysqld ``` #### Step 4: Secure MySQL Installation Run the MySQL secure installation script to improve the security of your MySQL installation: ```bash sudo mysql_secure_installation ``` Follow the prompts to configure security settings such as setting the root password, removing anonymous users, disallowing remote root login, and removing the test database. #### Step 5: Log In to MySQL Log in to the MySQL root user account to start managing your databases: ```bash sudo mysql -u root -p ``` Enter the root password you set during the secure installation process. #### Step 6: Create a New Database To create a new database, use the following SQL command: ```sql CREATE DATABASE database_name; ``` Replace `database_name` with your desired database name. #### Step 7: Create a New MySQL User Create a new MySQL user and grant them privileges on your new database: ```sql CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost'; FLUSH PRIVILEGES; ``` Replace `username`, `password`, and `database_name` with your actual values. #### Step 8: Test the New User Log out of the MySQL root account: ```sql exit ``` Log in with the new user to ensure they have the correct permissions: ```bash mysql -u username -p ``` Enter the password for the new user. Switch to the new database: ```sql USE database_name; ``` #### Step 9: Basic MySQL Commands Here are some basic MySQL commands to manage your databases: - **Show databases:** ```sql SHOW DATABASES; ``` - **Create a table:** ```sql CREATE TABLE table_name ( id INT AUTO_INCREMENT PRIMARY KEY, column1 VARCHAR(255) NOT NULL, column2 INT NOT NULL ); ``` - **Insert data into a table:** ```sql INSERT INTO table_name (column1, column2) VALUES ('value1', 123); ``` - **Query data from a table:** ```sql SELECT * FROM table_name; ``` #### Step 10: Backup and Restore Databases **Backup a database:** Use the `mysqldump` command to create a backup of your database: ```bash mysqldump -u username -p database_name > database_name_backup.sql ``` **Restore a database:** Use the `mysql` command to restore a database from a backup file: ```bash mysql -u username -p database_name < database_name_backup.sql ``` #### Conclusion You have successfully installed and configured MySQL on your CentOS VPS, created a new database and user, and learned basic management commands. Regularly backup your databases and keep your MySQL installation updated to ensure security and reliability. **Additional Resources:** - **MySQL Documentation:** [https://dev.mysql.com/doc/](https://dev.mysql.com/doc/) - **CentOS Documentation:** [https://www.centos.org/docs/](https://www.centos.org/docs/) - **MySQL Backup and Restore Guide:** [https://dev.mysql.com/doc/refman/8.0/en/backup-and-recovery.html](https://dev.mysql.com/doc/refman/8.0/en/backup-and-recovery.html)