completely erase and reinstall mysql script.

 Warning: this will delete ALL your databases.

After running this script it will let you automatically run mysql without a password (as root). Note this  is a security risk so do not run this on a production server.

#!/bin/bash

set -e

# Function to remove MySQL and clean up
remove_mysql() {
    echo "Removing MySQL server and packages..."
    sudo apt-get remove -y mysql-server mysql-client mysql-common

    echo "Removing MySQL directories..."
    sudo rm -rf /etc/mysql /var/lib/mysql /var/log/mysql /var/run/mysqld

    # Clean up any residual packages and update package info
    sudo apt-get autoremove -y
    sudo apt-get clean
}

# Function to ensure MySQL configuration file exists
ensure_mysql_config() {
    if [ ! -f /etc/mysql/mysql.cnf ]; then
        echo "Creating MySQL configuration file..."
        echo "[client]" | sudo tee /etc/mysql/mysql.cnf
        echo "user=root" | sudo tee -a /etc/mysql/mysql.cnf
        echo "password=abcd1234*" | sudo tee -a /etc/mysql/mysql.cnf
        echo "MySQL configuration file created at /etc/mysql/mysql.cnf."
    fi
}

# Function to create .my.cnf files in user directories
create_user_mysql_config() {
    echo "Configuring MySQL client for all users..."
    for home in /home/*; do
        if [ -d "$home" ]; then
            user=$(basename "$home")
            echo "[client]" > "$home/.my.cnf"
            echo "user=root" >> "$home/.my.cnf"
            echo "password=abcd1234*" >> "$home/.my.cnf"
            chown "$user:$user" "$home/.my.cnf"
            chmod 600 "$home/.my.cnf"
            su -c "mysql_config_editor set --host=localhost --user=root --password" -s /bin/bash "$user"
        fi
    done
}

is_running() {
echo "Checking MySQL service status..."
    if ! sudo systemctl is-active --quiet mysql; then
        sudo systemctl stop mysql
    fi
}

# Function to reinstall and configure MySQL
reinstall_mysql() {
    echo "Reinstalling MySQL server and client..."
    sudo apt-get update
    sudo apt-get install -y mysql-server mysql-client mysql-common

    # Ensure necessary MySQL configuration directories and files
    echo "Ensuring MySQL configuration files and directories..."
    sudo mkdir -p /etc/mysql /var/lib/mysql /var/log/mysql /var/run/mysqld
    sudo chown -R mysql:mysql /var/lib/mysql /var/run/mysqld
    sudo chmod 755 /var/run/mysqld

    echo "Ensure MySQL configuration file"
    ensure_mysql_config

    echo "Fixing broken packages and reconfiguring..."
    sudo dpkg --configure -a
    sudo apt-get install -f
}

# Function to reset MySQL root password
reset_mysql_password() {
    echo "Resetting MySQL root password..."

    # Ensure the MySQL directory for socket exists
    sudo mkdir -p /var/run/mysqld
    sudo chown mysql:mysql /var/run/mysqld
    sudo chmod 755 /var/run/mysqld

    # Start MySQL with --skip-grant-tables in the background
    sudo echo "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abcd1234*'; FLUSH PRIVILEGES;" > /var/lib/mysql/reset.txt

sudo chown mysql:mysql /var/run/mysqld
sudo chmod 777 /var/run/mysqld # you can make this 755 some other time
sudo mysqld --init-file=/var/lib/mysql/reset.txt  --user=mysql

    # Start MySQL normally
    sudo systemctl restart mysql
}

# Main script logic
echo "Do you want to remove MySQL packages and directories? (y/n): "
read answer

if [ "$answer" = "y" ] || [ "$answer" = "Y" ]; then
    remove_mysql
fi

is_running
create_user_mysql_config
ensure_mysql_config
reinstall_mysql
reset_mysql_password

echo "MySQL has been reinstalled (if needed), root password has been reset, and client configuration is updated for all users."

Comments

Post a Comment

Popular posts from this blog

what to do if you crashed your database OR it says there are no tables (but there are!)

Sometimes moving a database file ( /var/lib/mysql/* ) breaks the records inside mysql's brain about what databases it had and what their tables were. If you did not use mysqldump to take backups, you are in trouble. Unfortunately if you do this, you have a long night ahead of you. However, help is at hand. A symptom of breaking Moodle in this way is you get this error:     Config table does not contain version, can not continue, sorry.     which means it cannot read the table mdl_config. The basic steps are: 1. Move your crashed/confused databases out the way so you don't break them further.  2. Recreate your mysql instance. 3. Recreate the databases and tables WITHOUT their contents (just the database names and table structures). 4. Tell mysql to forget their contents without using drop command (you use discard instead) 5. move your original db files back into place 6. Tell mysql to re-scan (import) the db files. 7. Create a timed script to run backups Here are the detailed st
Read more

Backup a database

It's way too difficult to restore a database using the /var/lib/mysql files. It's better to use mysqldump . Assume your database is named mydbname . mysqldump -u root -p mydbname > mydbname.sql This will dump out the SQL needed to recreate the database. Normally you want this as a cron job so it runs daily. Google how to make a cron job. Put the command into a script. You also want to gzip it because it's quite big and repetitive. The below is an example of a useful script. It saves the files with dates in the names so you can revert/restore the database from any date. Note however it embeds your password into the file, this is probably a bad idea™ but you can find a better solution yourself. One way is to create an authenticated user, let them run mysql without password ( saved password ), and set them run the script, ie use the username parameter in cron. #!/bin/sh uniquedate=`date +"%Y%m%d_%H%M%S-%s"` backuppath="/var/log/mysqldumps" [ ! -d $back
Read more