John's MySQL blog

If you are trying to dump a database in a mysql instance running inside a docker container and you get the following error, despite logging in as a user who has read permission on it, it means that the user you are using does not have the required  PROCESS  privilege to perform certain tasks during the database dump. This is common when attempting to dump information such as  tablespaces , which may require elevated permissions. mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces fix with: mysqldump -u <username> -p --no-tablespaces <database_name> > dump.sql The --no-tablespaces option in mysqldump prevents the tool from including tablespace information in the dump. Details: Tablespace Information : MySQL uses tablespaces to store table data on disk, especially when dealing with InnoDB tables. When dumping a database, mysqldump by default tries to include metadat...

If mysql says you can't login as root but you have definitely got the right password and you have just installed it for the first time, it can be a nuisance. Do the below as user root. My suggestion is: 1. If you have existing databases, and want to keep them, back them up with mysqldump.       mysqldump -u user_with_access -pPassword database_to_rescue > database_to_rescue.sql Assuming that you have at least one user account, e.g. "user_with_access", who can read the database called “database_to_rescue". 2. Erase the mysql directory and start over:       service mysql stop rm -rf /var/lib/mysql/* 3. Re-run mysqld initialisation to set the password to blank: mkdir -p /var/run/mysqld chown -R /var/run/mysqld chmod 777 /var/run/mysqld # you can change this back to 755 later mysqld --initialize-insecure --user=mysql If it says it can't find the socket in /var/run/mysqld, repeat the commands mkdir-chown above. 4. It will then set up mysql from scratch w...

 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 ...

  Fix: ERROR 1396 (HY000): Operation CREATE USER failed in MySQL? This is a mysql 8 bug. Suppose the user is  myuser . 1. The user already exists. Delete them with DROP. (not DELETE).      DROP USER myuser.     DO NOT use "delete from mysql.user where user=myuser". 2. You already deleted them with DELETE. Try it again with DROP, even if they do not exist.     If you are too late and you already made the mistake in (1) above you can either create a new user with a different name, e.g. myuser2, or, repeat the drop statement above in (1). 3. The password doesn't meet the default MySQL 8 requirements.     Sometimes it is because the password isn't good enough. It must be 8 chars minimum, with at least one caps, one lower, one number, and one punctuation. 4. Check your mysql supports old passwords ( mysql_native_password).    Use  CREATE USER 'myuser'@'localhost' IDENTIFIED WITH  mysql_native_password  BY 's0m3Pa...

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...

  To save a table into a CSV (spreadsheet) file: select * from TableName INTO OUTFILE '/var/lib/mysql-files/spreadsheet.csv' FIELDS ENCLOSED BY '"' TERMINATED by ';' ESCAPED BY '"' LINES TERMINATED BY '\n' This will drop the file into the folder /var/lib/mysql-files/