Connecting Laravel to a MySQL Database

Connecting Laravel to a MySQL Database

Connecting your Laravel application to a MySQL database is a fundamental step in building web applications. Laravel makes this process straightforward with its robust database configuration system. In this comprehensive guide, you'll learn how to properly configure, connect, and interact with a MySQL database in your Laravel application.

Prerequisites

Before connecting Laravel to MySQL, ensure you have:

• A working MySQL installation
• Laravel application installed
• Database credentials (username, password, database name)
• Basic knowledge of MySQL and Laravel

Step 1: Install MySQL Database

Check MySQL Installation

Verify that MySQL is installed and running:

// Check MySQL version
mysql --version

// Check MySQL service status
sudo systemctl status mysql

// Or on Windows
net start mysql

Start MySQL Service

If MySQL is not running, start it:

// On Linux/Ubuntu
sudo systemctl start mysql
sudo systemctl enable mysql

// On Windows
net start mysql

Step 2: Create MySQL Database and User

Connect to MySQL

Connect to MySQL as root or an admin user:

// Connect as root
mysql -u root -p

// Or with specific host
mysql -u root -p -h localhost

Create Database

Create a new database for your Laravel application:

CREATE DATABASE laravel_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

// Verify database was created
SHOW DATABASES;

Create Database User

Create a dedicated user for your Laravel application:

CREATE USER 'laravel_user'@'localhost' IDENTIFIED BY 'YourStrongPassword123!';

// Grant privileges
GRANT ALL PRIVILEGES ON laravel_app.* TO 'laravel_user'@'localhost';

// Flush privileges
FLUSH PRIVILEGES;

// Verify user was created
SELECT user, host FROM mysql.user WHERE user = 'laravel_user';

Step 3: Configure Laravel Environment

Copy .env File

If you don't have a .env file, create it from .env.example:

cp .env.example .env

Configure MySQL Connection

Edit the .env file with your MySQL credentials:

APP_NAME=LaravelApp
APP_ENV=local
APP_DEBUG=true
APP_URL=http://localhost

LOG_CHANNEL=stack
LOG_DEPRECATIONS_CHANNEL=null
LOG_LEVEL=debug

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel_app
DB_USERNAME=laravel_user
DB_PASSWORD=YourStrongPassword123!

BROADCAST_DRIVER=log
CACHE_DRIVER=file
FILESYSTEM_DRIVER=local
QUEUE_CONNECTION=sync
SESSION_DRIVER=file

Step 4: Configure Database Configuration

Manual Configuration (Optional)

You can also configure database settings in config/database.php:

// config/database.php
'connections' => [
    'mysql' => [
        'driver' => 'mysql',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'prefix_indexes' => true,
        'strict' => true,
        'engine' => null,
        'options' => extension_loaded('pdo_mysql') ? array_filter([
            PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        ]) : [],
    ],
],

Step 5: Test Database Connection

Test with artisan

Use Laravel's built-in command to test the database connection:

php artisan db:show
php artisan db:table users
php artisan migrate:fresh --force

Test via Tinker

Use Laravel Tinker to test the connection:

php artisan tinker

>>> DB::connection()->getPdo()
=> object(PDO)

>>> DB::connection('mysql')->getDatabaseName()
=> "laravel_app"

>>> DB::select('SELECT 1')
=> [{"1": 1}]

Step 6: Create and Run Migrations

Generate Migration File

Create a migration for your users table:

php artisan make:migration create_users_table

// Or with model
php artisan make:migration create_users_table --create=users

Edit Migration File

Edit the migration file in database/migrations:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateUsersTable extends Migration
{
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('users');
    }
}

Run Migrations

Execute the migrations to create tables:

php artisan migrate

// Rollback migrations
php artisan migrate:rollback

// Reset and run all migrations
php artisan migrate:fresh

// Run migrations in production
php artisan migrate --force

Step 7: Configure MySQL Advanced Settings

Character Set and Collation

Ensure proper character support for multi-language applications:

// In .env file
DB_CHARSET=utf8mb4
DB_COLLATION=utf8mb4_unicode_ci

Connection Pooling

Configure connection pooling for better performance:

// config/database.php
'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
    'options' => [
        PDO::ATTR_PERSISTENT => true,
        PDO::ATTR_EMULATE_PREPARES => false,
    ],
],

Step 8: Handle Common Database Issues

Permission Issues

If you encounter permission errors:

// Grant proper permissions
GRANT ALL PRIVILEGES ON laravel_app.* TO 'laravel_user'@'localhost';

// Set file permissions
sudo chown -R www-data:www-data /var/www/your-laravel-app/storage
sudo chmod -R 755 /var/www/your-laravel-app/storage

Connection Timeout

Increase connection timeout for long-running queries:

// In .env file
DB_POOL=20
DB_TIMEOUT=60

Step 9: Database Backup Strategy

Create Backup Script

Create a script to backup your MySQL database:

#!/bin/bash
BACKUP_DIR="/var/backups/laravel"
DATE=$(date +%Y%m%d_%H%M%S)
FILE="laravel_app_$DATE.sql"

# Create backup
mysqldump -u laravel_user -pYourStrongPassword123! laravel_app > "$BACKUP_DIR/$FILE"

# Compress backup
gzip "$BACKUP_DIR/$FILE"

# Clean old backups (keep last 7 days)
find $BACKUP_DIR -name "*.gz" -mtime +7 -delete

Schedule Backups

Add to your crontab for automated backups:

0 2 * * * /path/to/backup/script.sh

Step 10: Monitor Database Performance

Enable Query Logging

Log slow queries for debugging:

// In .env file
DB_LOG_SQL=true
DB_LOG_CONNECTION=true

// Or in config/database.php
'connections' => [
    'mysql' => [
        'driver' => 'mysql',
        'log_queries' => env('DB_LOG_SQL', false),
        'log_connections' => env('DB_LOG_CONNECTION', false),
        // ... other settings
    ],
],

Best Practices

• Use strong, complex passwords for database users
• Limit database user privileges to only what's needed
• Regularly backup your database
• Use environment variables for sensitive data
• Monitor database performance regularly
• Use proper character sets (utf8mb4) for full Unicode support

Conclusion

Connecting Laravel to MySQL is straightforward once you understand the configuration process. By following these steps, you'll have a properly configured MySQL database connection that's secure, performant, and ready for production use.

Remember these key points:

• Create a dedicated database user with appropriate privileges
• Configure the .env file with correct connection details
• Test the connection before running migrations
• Implement proper backup and monitoring strategies
• Follow security best practices

With your Laravel application properly connected to MySQL, you're ready to build powerful, data-driven web applications!

Happy coding! 🚀