MySQL Setup and Your First Database

01What Is MySQL and Why Choose It?

MySQL is one of the world's most widely used relational database systems: it stores data in tables and is queried with the SQL language. Many kinds of software, such as websites, e-commerce systems, reservation and inventory applications, use MySQL or the compatible MariaDB behind the scenes.

MySQL has two release tracks: LTS releases with long-term support, and innovation releases that bring new features sooner. Choose LTS for business projects and for learning, and check which release is the current LTS on the dev.mysql.com download page.

In this guide we install MySQL Community Server, the free community edition. If you only want to experiment quickly with PHP, a ready-made package that includes MariaDB will also do; we covered it in our XAMPP installation guide.

02Installing MySQL on Windows

On the download page, choose the MSI installer for Windows. In current releases, MySQL Configurator opens after installation and walks you through the server settings step by step; older releases did the same job with MySQL Installer. If the download page asks you to sign in with an Oracle account, you can download directly with the "No thanks, just start my download" link.

1. For the setup type on a development machine, choose the Development Computer profile; it keeps memory usage low.
2. In the connection settings, keep the default port 3306; if another database on the machine uses this port, enter a value such as 3307.
3. For the authentication method, keep the recommended strong encryption option.
4. Set the root password and note it down somewhere safe; this is the most privileged account on the server.
5. Register MySQL as a Windows service and choose to start it at boot.
6. In the final step, apply the settings; when every step shows a green check, the server is ready.

03Installing on macOS and Linux

On macOS, the official DMG package installs a system preferences panel from which you start and stop the server. If you use Homebrew, installation and service management take just two commands.

On Ubuntu and Debian, the MySQL server is available in the distribution repository. On Ubuntu, the root user is authenticated by default with the auth_socket method after installation; that is, you connect with the sudo mysql command instead of a password. On both systems we recommend running the mysql_secure_installation script after installation: it removes anonymous users and the test database and disables remote root login.

brew install mysql
brew services start mysql
mysql_secure_installation

sudo apt update
sudo apt install mysql-server
sudo systemctl status mysql

# Security settings
sudo mysql_secure_installation

# Connecting as root (auth_socket)
sudo mysql

04Verifying the Installation

Use the commands below to check that the server is running and that you can connect. The mysql -u root -p command asks for your password; if you see the mysql> prompt with the correct password, the installation is complete. Do not forget the semicolon at the end of SQL commands inside the server.

mysql --version
mysql -u root -p

SELECT VERSION();
SHOW DATABASES;
EXIT;

05Creating a MySQL Database

When creating a new database, choosing the right character set from the start prevents character-encoding and emoji problems later. In current MySQL releases the default character set is already utf8mb4; it is still a good habit to state it explicitly in the command. If you want language-specific sorting rules, such as Turkish, you can use a collation like utf8mb4_tr_0900_ai_ci.

Next, let's create the first table. The example has a simple products table: the DECIMAL type is used for price instead of floating point, because rounding errors are unacceptable in monetary amounts.

• PRIMARY KEY uniquely identifies each row; AUTO_INCREMENT assigns the number automatically.
• The InnoDB storage engine supports transactions and foreign keys and is the default in current releases.
• Adding indexes to columns you search often speeds up queries; but every unnecessary index slows down writes.

CREATE DATABASE magaza
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_tr_0900_ai_ci;

USE magaza;

CREATE TABLE urunler (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ad VARCHAR(150) NOT NULL,
  fiyat DECIMAL(10,2) NOT NULL,
  stok INT NOT NULL DEFAULT 0,
  olusturma DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO urunler (ad, fiyat, stok) VALUES ('Sample product', 10.00, 25);
SELECT * FROM urunler;

06Creating MySQL Users and Privileges

Never connect your application to the database as the root user. For each application, create a separate user that can access only its own database and perform only the operations it needs. That way, even if a security hole appears, the damage stays limited to that database.

The 'localhost' part next to the username determines where connections are accepted from. If the application and the database are on the same server, localhost is enough; if you need a remote connection, allow only a specific IP address and avoid opening it to everyone with '%'.

CREATE USER 'magaza_app'@'localhost' IDENTIFIED BY 'Guclu-Bir-Sifre-2!';

GRANT SELECT, INSERT, UPDATE, DELETE ON magaza.* TO 'magaza_app'@'localhost';

SHOW GRANTS FOR 'magaza_app'@'localhost';

-- Changing a password and deleting a user
ALTER USER 'magaza_app'@'localhost' IDENTIFIED BY 'Yeni-Sifre-3!';
DROP USER 'magaza_app'@'localhost';

07Visual Management with MySQL Workbench

If you prefer a graphical interface to the command line, MySQL Workbench is MySQL's official desktop tool. On the connection screen you click the plus icon and enter the server address (127.0.0.1), the port (3306) and the username; the Test Connection button verifies the settings.

In Workbench you can design tables visually, write queries and view results as a table, and export and import data from the Server menu. In the query editor, Ctrl+Enter runs the command under the cursor.

Backup and restore

The export screen in Workbench uses the mysqldump tool in the background. You can do the same from the terminal, and to take regular automatic backups this command can be added to a scheduled task. Unless you test now and then that a backup can be restored, you have not really made a backup.

# Yedek al
mysqldump -u root -p magaza > magaza.sql

# Restore
mysql -u root -p magaza < magaza.sql

08Common Connection Errors and Fixes

Most problems in MySQL involve connection, authentication and password policy. The error number is the fastest way to understand the cause.

• ERROR 1045 Access denied: The username, password or connection address (localhost / 127.0.0.1) is wrong. Check which address the user was defined for with SELECT user, host FROM mysql.user;.
• ERROR 2002 / 2003 Can't connect: The server is not running. On Windows, start the MySQL service from the Services screen; on Linux, use sudo systemctl start mysql.
• ERROR 1819 password policy: The validate_password component is rejecting a weak password; choose a longer one with uppercase letters, digits and special characters.
• Port 3306 in use: Another database such as XAMPP is running on the machine; stop one of them or change one of the ports.
• Old client authentication error: Current MySQL uses the caching_sha2_password method by default; update your client library.

09Connecting MySQL to Your Application

In PHP projects, the recommended way to connect to MySQL is to use prepared statements with PDO; this method provides basic protection against SQL injection. Instead of writing connection details into your code, keep them in an environment file that is not committed to Git. To manage PHP dependencies, see our Composer installation guide.

If you are considering a different database, you can compare the two systems with our PostgreSQL installation article. For more content, browse the database tag page.

Database design directly affects the speed of a website or e-commerce platform: wrong indexes or unnecessary queries make pages slow to open. We measure and remove such bottlenecks in our speed and performance optimization work, and in our corporate website projects we build the database with this in mind from day one.

<?php
$pdo = new PDO(
    'mysql:host=localhost;dbname=magaza;charset=utf8mb4',
    'magaza_app',
    getenv('DB_SIFRE'),
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

$sorgu = $pdo->prepare('SELECT ad, stok FROM urunler WHERE id = ?');
$sorgu->execute([1]);
print_r($sorgu->fetch());