MySQL is a relational database management system developed by MySQL AB (founded by Michael Widenius) from Sweden. It was acquired by Sun Microsystems in 2008 for $1 billion, and subsequently Oracle acquired Sun in 2009. MySQL organizes data in separate tables rather than a single large repository, which enhances performance and flexibility. As an open-source solution, it doesn't require additional licensing fees and can be customized under the GPL license. The system supports large-scale databases, handling tens of millions of records efficiently. It uses standard SQL and runs on multiple platforms with support for various programming languages including C, C++, Python, Java, Perl, PHP, Eiffel, Ruby, and Tcl.
Offline Installation Process
Downloading from Official Website
Navigate to the official MySQL website at https://www.mysql.com, select the Community Server edition, choose the appropriate version, and initiate the download.
Extracting the Installation Package
Upload the downloaded MySQL package to your Linux server and extract it using the following command:
tar -xvf mysql-5.7.31-1.el7.x86_64.rpm-bundle.tar
Pre-installation Cleenup
Before installation, check for existing MySQL installations:
rpm -qa | grep -i mysql
Also check for MariaDB installations:
rpm -qa | grep -i mariadb
Uninstall any existing MySQL or MariaDB installations using:
rpm -e --nodeps <package_name>
Removing Residual Files
Clean up residual files to ensure a fresh installation:
rm -rf /var/lib/mysql/*
rm -f /var/log/mysqld.log
Checking Dependencies
Verify required dependencies are installed:
rpm -qa | grep libaio
rpm -qa | grep net-tools
Installation Comands
Execute the following commands in sequence to install MySQL components:
rpm -ivh mysql-community-common-5.7.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.31-1.el7.x86_64.rpm
If you encounter signature verification issues, use the following command:
rpm -ivh mysql-community-server-5.7.31-1.el7.x86_64.rpm --force --nodeps
Verification
Confirm successful installation:
rpm -qa | grep -i mysql
Check the MySQL version:
mysqladmin --version
Directory Structure
| Parameter | Path | Descritpion | Notes |
|---|---|---|---|
| --basedir | /usr/bin | Command directory | mysqladmin, mysqldump, etc. |
| --datadir | /var/lib/mysql/ | Database file location | |
| --plugin-dir | /usr/lib64/mysql/plugin | Plugin storage path | |
| --log-error | /var/log/mysqld.log | Error log path | |
| --pid-file | /var/run/mysqld/mysqld.pid | Process PID file | |
| --socket | /var/lib/mysql/mysql.sock | Unix socket for local connections | |
| /usr/share/mysql | Configuration directory | Scripts and config files | |
| /etc/my.cnf | Main configuration file | cat /etc/my.cnf |
Service Management
Service control commands:
# Start service
systemctl start mysqld
# Stop service
systemctl stop mysqld
# Restart service
systemctl restart mysqld
# Check status
systemctl status mysqld
Automatic Startup
Configure MySQL to start automatically on boot:
# Check current startup status
systemctl list-unit-files | grep mysqld.service
# Enable automatic startup
systemctl enable mysqld
# Reload service configuration
systemctl daemon-reload
# Disable automatic startup (if needed)
systemctl disable mysqld
Process Verification
Check running MySQL processes:
ps -ef | grep -i mysql
Configuration
Initial Password Setup
After installation, MySQL generates a temporary root password which can be found in the log file:
grep 'temporary password' /var/log/mysqld.log
Connect to MySQL and set a new password:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123!';
Password Policy
MySQL 5.7 includes a password validation plugin that enforces complex password requirements. To disable this policy, edit the configuration file:
[mysqld]
validate_password = off
Restart the service to apply changes:
systemctl restart mysqld
Character Set Configuration
By default, MySQL uses latin1 character set. To support UTF-8:
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
Restart the service and verify:
SHOW VARIABLES LIKE 'character%';
Modifying Existing Database Character Sets
To change character sets for existing databases and tables:
-- Change database character set
ALTER DATABASE database_name CHARACTER SET 'utf8';
-- Change table character set
ALTER TABLE table_name CONVERT TO CHARACTER SET 'utf8';
Remote Connection Setup
Grant remote access to the root user:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'YourPassword' WITH GRANT OPTION;
Restart the service:
systemctl restart mysqld
Firewall Configuration
Ensure the firewall allows MySQL connections:
# Check firewall status
firewall-cmd --state
# Open port 3306
firewall-cmd --zone=public --add-port=3306/tcp --permanent
# Reload firewall
firewall-cmd --reload
User Management
User Table Structure
The MySQL user table contains several important fields:
- host: Connection type (%, IP address, hostname, or localhost)
- user: Username
- authentication_string: Encrypted password (MySQL 5.7+)
- select_priv, insert_priv, etc.: Individual permissions
Creating Users
Create a new user with specified password:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'userpassword';
Modifying Passwords
Update an existing user's password:
UPDATE mysql.user SET authentication_string = PASSWORD('newpassword') WHERE user = 'newuser';
Apply changes:
FLUSH PRIVILEGES;
Modifying Usernames
Change a username:
UPDATE mysql.user SET user = 'newusername' WHERE user = 'oldusername';
Deleting Users
Properly remove a user:
DROP USER 'username'@'host';
Apply changes:
FLUSH PRIVILEGES;
Permission Management
Granting Permissions
Grant specific privileges to a user:
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'host';
Grant all privileges:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'host' IDENTIFIED BY 'password';
Viewing Permissions
Check current user permissions:
SHOW GRANTS;
View specific user permissions:
SELECT * FROM mysql.user WHERE user = 'username' \G;
Revoking Permissions
Remove specific permissions:
REVOKE SELECT, INSERT, UPDATE, DELETE ON database_name.* FROM 'username'@'host';
Apply changes:
FLUSH PRIVILEGES;
Revoke all permissions:
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'host';
Apply changes:
FLUSH PRIVILEGES;