Facebook Twitter Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    You are at:Home»Databases»MySQL»A Comprehensive Guide to Changing the Default MySQL Data Directory on Ubuntu & Debian

    A Comprehensive Guide to Changing the Default MySQL Data Directory on Ubuntu & Debian

    By RahulApril 9, 20234 Mins Read

    MySQL is a widely-used open-source relational database management system (RDBMS) that powers numerous web applications and services. By default, MySQL stores its data in a predefined directory on Ubuntu and Debian systems, which may not always be suitable for your requirements. For instance, you might want to move the data directory to another location for better performance, security, or to utilize a separate disk or partition.

    Advertisement

    In this comprehensive guide, we will walk you through the process of changing the default MySQL data directory on Ubuntu and Debian systems, ensuring a smooth transition with minimal downtime.

    Step 1: Preparing the New Data Directory

    The first step is to create a new directory where you want to store the MySQL data. Ensure that the new location has adequate storage space for your current and future data requirements. Create the new directory using the following command, replacing /disk2/mysql/data with your desired path:

    sudo mkdir -p /disk2/mysql/data 
    

    Next, set the ownership and permissions for the new directory to match those of the default MySQL data directory:

    sudo chown -R mysql:mysql /disk2/mysql/data 
    sudo chmod 750 /disk2/mysql/data 
    

    Step 2: Stop the MySQL Service

    Before making any changes to the MySQL configuration, you must stop the MySQL service to avoid data corruption or loss. Use the following command to stop the service:

    sudo systemctl stop mysql 
    

    Step 3: Copy the Existing Data to the New Directory

    Now that the MySQL service is stopped, it is safe to copy the existing data to the new directory. To preserve file permissions and ownership, use the rsync command:

    sudo rsync -av /var/lib/mysql/ /disk2/mysql/data 
    

    Replace /var/lib/mysql/ with the current MySQL data directory path if it differs on your system.

    Step 4: Update the MySQL Configuration

    To inform MySQL of the new data directory, you must update the configuration file. Open the MySQL configuration file (typically located at /etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu and /etc/mysql/mariadb.conf.d/50-server.cnf on Debian) using your preferred text editor:

    sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf 
    

    Locate the [mysqld] section and update the datadir and socket options to point to the new directory:

    1
    2
    3
    [mysqld]
    datadir=/disk2/mysql/data
    socket=/disk2/mysql/data/mysqld.sock

    If these options do not exist, add them to the [mysqld] section. Save and close the configuration file.

    Step 5: Update the MySQL Client Configuration

    To ensure that MySQL clients can connect to the server using the new socket file, update the client configuration. Open the MySQL client configuration file (usually located at /etc/mysql/mysql.conf.d/mysql.cnf on Ubuntu and /etc/mysql/mariadb.conf.d/50-client.cnf on Debian) with a text editor:

    sudo nano /etc/mysql/mysql.conf.d/mysql.cnf 
    

    Locate the [client] section and update the socket option to point to the new directory:

    1
    2
    [client]
    socket=/disk2/mysql/data/mysql.sock

    If the socket option does not exist, add it to the [client] section. Save and close the configuration file.

    Step 6: Update the AppArmor Configuration

    If your system uses AppArmor for security, you may need to update the AppArmor configuration for MySQL to allow access to the new data directory. Open the AppArmor configuration file (usually located at /etc/apparmor.d/usr.sbin.mysqld) with a text editor:

    sudo nano /etc/apparmor.d/usr.sbin.mysqld 
    

    Add the following lines, replacing /disk2/mysql/data with your new data directory path:

    1
    2
    /disk2/mysql/data/ r,
    /disk2/mysql/data/** rwk,

    Save and close the file. Reload the AppArmor configuration using the following command:

    sudo systemctl restart apparmor 
    

    Step 7: Start the MySQL Service

    Now that the necessary configurations have been updated, start the MySQL service with the following command:

    sudo systemctl start mysql 
    

    Step 8: Verify the Changes

    To verify that the changes have been applied successfully, check the MySQL status and ensure that it is running:

    sudo systemctl status mysql 
    

    Additionally, you can connect to the MySQL server using a client such as the mysql command-line tool and verify that the new data directory is being used:

    mysql -u root -p -e 'SHOW VARIABLES WHERE Variable_Name = "datadir";' 
    

    This command should display the new data directory path.

    Step 9: Remove or Backup the Old Data Directory (Optional)

    Once you have confirmed that MySQL is using the new data directory and everything is working as expected, you can either remove the old data directory or create a backup for safekeeping:

    sudo rm -rf /var/lib/mysql 
    

    Or

    sudo mv /var/lib/mysql /var/lib/mysql_backup 
    

    Replace /var/lib/mysql with the current MySQL data directory path if it differs on your system.

    Conclusion

    Changing the default MySQL data directory on Ubuntu and Debian systems is a straightforward process that can help improve performance, security, or better utilize available storage resources. By following this comprehensive guide, you can easily move your MySQL data to a new location, update the necessary configurations, and ensure a seamless transition with minimal downtime.

    data MySQL
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    A Comprehensive Guide to Optimizing MySQL Table

    Create a Basic CRUD API in NodeJS, ExpressJS and MySQL

    How to Optimize MySQL Parameters for a 32 GB RAM System

    Add A Comment

    Leave A Reply Cancel Reply

    Advertisement
    Recent Posts
    • Git Switch vs. Checkout: A Detailed Comparison with Examples
    • How To Block Specific Keywords Using Squid Proxy Server
    • How To Block Specific Domains Using Squid Proxy Server
    • A Comprehensive Look at the Simple Mail Transfer Protocol (SMTP)
    • Understanding Basic Git Workflow: Add, Commit, Push
    Facebook Twitter Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.