Installing and setting up MySQL
MySQL backend support in PHP programming or installing and setting up MySQL database software on multiple OS operating systems (Windows, Apple Mac, Linux, and Android) requires following several graphical wizards and command-based instructions. These guide you through the basic steps of manually configuring and setting up MySQL database software for your database needs, and then configuring it as a backend database in PHP programming.
Installing MySQL Database Software on Different Operating Systems
Installing MySQL Software on Linux (Ubuntu/Debian).
Update the Operating System Package Index – To install MySQL software on a Linux operating system, first, in Linux, Ubuntu, or Debian versions, ensure that the installed software package list in the operating system is updated to the latest version.
Command to update the Ubuntu/Debian package list.
sudo apt update
Install the MySQL server software – Install the MySQL software on the console screen by executing the following command in a terminal root window, depending on your current version of Linux, Ubuntu, or Debian.
sudo apt install mysql-server
Start and enable MySQL on your computer – After manually installing the MySQL software, the MySQL database software should start automatically. Users can manually check the status of the MySQL software installation with the following command.
sudo systemctl status mysql
To manually start the MySQL software on your Linux computer, if it doesn’t start automatically after installation, run the following command.
sudo systemctl start mysql
To automatically start the MySQL database software upon boot, run the following command.
sudo systemctl enable mysql
Secure MySQL Software Installation – MySQL database software is installed with a security script that can make an existing MySQL software installation more secure by setting the database user’s root password, removing the insecure default MySQL root password, and improving MySQL security settings.
sudo mysql_secure_installation
Follow the on-screen instructions to properly configure the MySQL database software’s manual settings. For example,
- Set a new root password for the MySQL database.
- Delete the test database from MySQL.
- Disable any remote root logins to the MySQL software (for security purposes).
- Reload the admin administrator access tables in the MySQL database.
Access MySQL on Ubuntu Debian operating systems – After proper installation and configuration of the MySQL software, log in to the MySQL database software as the root user on your computer.
sudo mysql -u root -p
Here you will be prompted to enter your new root password that you previously set. Once properly logged in, you will be at the MySQL prompt, and can perform database creation-related activities.
Installing MySQL software on a Linux CentOS/RHEL distro.
Install the MySQL repository on CentOS/RHEL operating systems – First, on your Linux CentOS or RHEL operating system version, the MySQL database software is not available in the default repository, so users must first install the MySQL software repository on their CentOS/RHEL operating system.
MySQL database software default repository installation command.
sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
Install MySQL Server in CentOS/RHEL OS.
sudo yum install mysql-community-server
Start MySQL in CentOS/RHEL OS – Start the MySQL service on your computer.
sudo systemctl start mysqld
Secure MySQL installation in CentOS/RHEL OS – Like Ubuntu Linux, run a security script to increase the security of the MySQL database software.
sudo mysql_secure_installation
Check MySQL status in CentOS/RHEL OS – Database users can check the MySQL database software installation on their computer.
sudo systemctl status mysqld
Access the MySQL software in CentOS/RHEL OS – Access the MySQL software installed in CentOS/RHEL OS by logging into MySQL as the root user.
sudo mysql -u root -p
Installing the MySQL database software on macOS.
First, install Homebrew on your Mac operating system, if it isn’t already installed. If you don’t already have Homebrew installed, you can install it by running the following command in a Terminal window on your Apple Mac OS.
/bin/bash -c “$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)”
Install MySQL using Homebrew on Mac OS – Use Homebrew to install the MySQL database software on your Mac OS.
brew install mysql
Start the MySQL software on Apple Mac OS – After the proper MySQL database software installation on an Apple Mac, you can start the MySQL service as follows.
brew services start mysql
Secure MySQL installation in Apple Mac OS – Like Ubuntu and Linux operating systems, you can run the MySQL database software on Apple OS.
mysql_secure_installation
Access the MySQL software in Apple Mac OS – After the MySQL database software starts on Apple Mac OS, you can log in with the root user login.
mysql -u root -p
Installing MySQL on Microsoft Windows operating systems.
First, download the MySQL installer – First, visit the official MySQL website and manually download the MySQL software installer for Windows OS.
Run the MySQL installer in Windows OS – Open the MySQL installer on your Windows hard drive and select the appropriate setup file.
Developer Default – Installs the MySQL server and necessary tools for database development on Windows OS.
Server Only – Installs only the MySQL server on Windows.
Custom – This allows the MySQL user to select specific software components.
Follow the MySQL setup wizard – During the MySQL setup installation on Windows, the installation wizard will ask the user to perform the following tasks:
Set the root password for your MySQL database environment.
Manually configure the necessary MySQL server settings (port, server type, etc.)
And select the database authentication method.
Start the MySQL software on Windows – After the MySQL database software is installed, the MySQL services will start automatically. Here, users can check the Windows Services application status.
Access the MySQL software on Windows – After the MySQL software is installed, users can open the MySQL command line client software and log in to the database with the database root password set during the MySQL software installation.
Basic configuration and use of the MySQL database software.
After installing and starting the MySQL database software on their computer, database users can create databases, tables, and manual user accounts.
Here’s a basic guide to help you get started with MySQL.
Creating a Database in MySQL.
After logging into the MySQL database software on their operating system, users can create a new database using the CREATE DATABASE statement command.
CREATE DATABASE test_database;
Creating Users and Granting Permissions in MySQL.
MySQL users can create new user accounts and grant each database user specific database access and modification privileges.
Create a new user in MySQL.
CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’;
Grant permissions to a database user – To grant a database user full privileges on the database in MySQL.
GRANT ALL PRIVILEGES ON my_database.* TO ‘username’@’localhost’;
To grant the MySQL database user limited privileges, such as read-only access.
GRANT SELECT ON my_database.* TO ‘username’@’localhost’;
Apply mysql database changes – After granting privileges to a MySQL database user, you must reload the privileges.
FLUSH PRIVILEGES;
Creating new tables in mysql database software.
To create a new table in a database, you can use the CREATE TABLE command statement.
mysql new table creation command.
USE test_database;
CREATE TABLE employee (
id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(100),
email VARCHAR(100) UNIQUE
);
Inserting data into MySQL database tables.
Once a database table is created, users can insert new table data into it using the INSERT INTO MySQL command.
INSERT INTO employee (emp_name, email) VALUES (‘Siddhi’, ‘siddhi@domain.com’);
INSERT INTO employee (emp_name, email) VALUES (‘Harry’, ‘harry@domain.com’);
Querying data from a MySQL database table.
In MySQL, database users can retrieve data from database tables using the SELECT query command.
SELECT * FROM employee;
To filter data in a database table, database users can add a WHERE clause to the table.
SELECT * FROM employee WHERE name = ‘Siddhi’;
Updating and deleting data from a MySQL database table.
Updating existing table records in a MySQL database table.
UPDATE employee SET email = ‘harry@domain.com’ WHERE name = ‘Harry’;
Deleting records from a database table.
DELETE FROM employee WHERE name = ‘Harry’;
Testing and Troubleshooting a MySQL Installation.
After installing and starting MySQL on your computer, users may encounter some common problems. Here are some tips for troubleshooting common database problems.
Check the MySQL services on your computer.
Check the MySQL services running on your computer. MySQL users can check the software status using the following commands.
Check MySQL services on Linux & Ubuntu.
sudo systemctl status mysql
Check MySQL services on macOS & Homebrew.
brew services list
Windows OS – Open Services on your Windows computer and check if the MySQL services are started.
MySQL software errors.
If the MySQL software on your computer is not starting, or you are receiving various errors related to the MySQL software, You should check the MySQL error log (located in /var/log/mysql/ on Linux or C:\ProgramData\MySQL\MySQL Server <version>\data\ on Windows).
MySQL software connectivity issues.
If database users are unable to connect to the MySQL software, users should ensure that MySQL is listening on the correct port (3306 by default).
Check your computer’s firewall settings and ensure that the MySQL port is open.
Conclusion on Installing and Setting Up MySQL.
Now that you have installed and set up the MySQL database software on your computer, you can create and manage relational databases. Here, users can create a new database, create tables, insert and query data in existing databases, and manage database user privileges and administrator permissions. Remember that MySQL database software is a powerful and universal database system that is used as a backend database in many applications, especially web applications.
