How Do I Start and Stop MySQL?

MySQL is a popular open-source relational database management system used for storing, retrieving, and managing data. It powers many complex web applications and high traffic services across organizations. As a DBA or full-stack developer responsible for managing MySQL in production, you need to know how to properly start, stop and restart the MySQL service on demand.

This comprehensive guide covers all aspects around starting and stopping MySQL on both Linux and Windows platforms.

Prerequisites

Before starting or stopping MySQL, ensure:

• MySQL server is installed on your system
• You have administrative privileges to start/stop services
• The data directory, log files are present with proper permissions

Starting MySQL

When the MySQL server starts, it follows the order – initialize components, connect to data directory, start networking/Threads, reconnect client connections etc.

MySQL Startup Methods in Linux

On Linux, MySQL can be started in many ways:

• Using the systemd service
• Via init scripts in SysVinit systems
• Using chkconfig utility
• Manually running the mysqld process

For production usage, using systemd or init scripts is recommended. This offers better process monitoring and handling.

Here is an example using systemd:

sudo systemctl start mysql

To verify successful startup:

 
sudo systemctl status mysql

In older Linux systems using init scripts:

sudo /etc/init.d/mysql start

The chkconfig utility can be used to configure MySQL to start at system boot time:

sudo chkconfig mysql on

Optimizing MySQL Startup on Linux

For faster startup times, tune the following config parameters based on server resources:

• innodb_buffer_pool_size – Set between 50-80% of total memory
• innodb_log_file_size – Balance startup time with throughput
• max_connections – Set per expected application load

Also ensure:

• The machine has enough RAM, fast storage for data files
• Unused databases are removed
• Tables have proper indexes

MySQL Service on Windows

On Windows, MySQL runs as a standard Windows service. There are several methods to start the MySQL service:

1. Using the command prompt:

   net start mysql

2. Via Services manager GUI
3. Through the Task Manager
4. Setting registry parameters

For example, using Services manager:

1. Open Services panel
2. Locate MySQL service
3. Double click and choose Start option

Set MySQL Windows Service to Start at Boot

The MySQL Windows service can also be configured to start up automatically when the server boots up:

1. Open a command prompt as Administrator
2. Run

   sc config mysql start= auto

Now MySQL will be started as part of Windows services startup.

Stopping MySQL

When shutting down mysqld process gracefully, it performs clean up – flushing table data to disks, closing connections etc.

Force shutdowns should be avoided to prevent crashes or data loss.

Stop MySQL in Linux

To stop MySQL, use:

sudo systemctl stop mysql 

This sends a TERM signal to shut it down cleanly.

Verify stop status using:

sudo systemctl status mysql

For init based scripts:

sudo /etc/init.d/mysql stop

Optimize MySQL Shutdown

To tune shutdown timing and system stability:

• Set innodb_fast_shutdown option to faster shutdowns
• Increase innodb_shutdown_log_tries for avoiding incomplete shutdowns
• Set enough time using innodb_page_cleaners to flush dirty pages

Also review logs for any shutdown errors or warnings.

Stop MySQL Service on Windows

Methods to stop MySQL on Windows:

• Services manager GUI
• Command prompt:

  net stop mysql

• Task manager – locate mysqld process and kill it

In Services manager:

1. Open Services panel
2. Locate MySQL service
3. Double click it and choose Stop option

After stopping, verify in logs that shutdown was clean – check error logs in C:\ProgramData\MySQL\MySQL Server 8.0\Data\

Restarting MySQL

Restarting helps activate new configuration changes or clean out temporary data.

MySQL Restart in Linux

To restart MySQL:

sudo systemctl restart mysql

This runs stop followed by start sequence.

Alternatively, run:

 
sudo systemctl stop mysql
sudo systemctl start mysql

For SysVinit based systems:

sudo /etc/init.d/mysql restart 

Types of MySQL Restarts on Linux

Different restart types in MySQL 8.0:

• Normal – restarts after current queries finish
• Immediate – interrupts queries midway
• Fast – aborts running queries, no new connections permitted

To use immediate restart:

mysqladmin shutdown -u root -p --immediate

OR fast restart:

 
mysqladmin shutdown -u root -p --fast  

MySQL Restart on Windows

To restart MySQL service:

1. Services manager > Find MySQL > Restart
2. Command prompt:

   net stop mysql
   net start mysql 

Or use the MySQL command line:

mysqladmin shutdown -u root -p
net start mysql

When to Restart vs Flush Tables

Restart is required when:

• Configuration files are edited
• To load new runtime variables
• Switching storage engines

Flush tables can be used:

• Reopen non-MyISAM tables
• To defragment tables
• Has lower impact since connections are intact

So analyze situation to pick right option.

When to Start/Stop MySQL

Besides starting MySQL at system bootup, you may need to manually stop/start the service for:

To Start MySQL

• Migrating data, upgrading schema
• Moving MySQL data directory to new location
• Changes to memory parameters, storage configuration
• Enabling networking or new security policies

Reasons to Stop MySQL

• Emergency situation – runaway process impacting stability
• Server maintenance – upgrade OS, firmware, hardware changes
• Modifying configuration file options
• Blocking new connections for analytics

So based on situation, restart, shutdown or flush tables as applicable.

Common FAQs

Why is MySQL not starting?

Some common reasons and solutions:

• Configuration issue – Validate config file, data directory access
• Port conflict – Check if port 3306 already being used
• Insufficient memory – Confirm memory allocation parameters
• Data directory missing – Recover files or reinitialize database

Also review error logs for exact failure reason.

Should I stop MySQL before server reboot?

MySQL shutdown before reboot is not mandatory but a best practice for controlled restarts. Abrupt reboots make recovery slower.

Steps would be:

1. Stop MySQL service
2. Reboot server
3. Start MySQL once server is up

This avoids potential file system or hardware issues corrupting database.

How to activate read_only mode before shutdown?

To set read_only mode:

mysql -u root -p -e "SET GLOBAL read_only = ON;"
mysqladmin shutdown

This allows existing connections to continue while disallowing updates. Useful before planned maintenance.

Conclusion

Properly starting and stopping MySQL is critical for a DBA or devops engineer to keep the database server available and stable. Using the appropriate platform-specific commands along with the optimization, troubleshooting and high availability techniques discussed can help manage the MySQL service effectively. Going beyond a simple start and stop of the service, understand the internal processes, server needs and standard administration procedures.