John's MySQL blog

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

  $ sudo /usr/local/mysql/support-files/mysql.server start

create a file called /var/lib/mysql/reset.txt file put the "alter user" statement inside it, like so: echo " ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abcd1234*;  FLUSH PRIVILEGES; " > /var/lib/mysql/reset.txt run service mysql stop mkdir -p /var/run/mysqld chown mysql:mysql /var/run/mysqld chmod 777 /var/run/mysqld # you can make this 755 some other time mysqld --init-file=/var/lib/mysql/reset.txt  --user=mysql Now do this to make it remember that you can login as root, this will let you just run “mysql” to run it. Security risk obviously. user=`whoami` su -c "mysql_config_editor set --host=localhost --user=root --password" -s /bin/bash “$user" When it asks for a password, give your mysql password, e.g. abcd1234*

  When creating the app database, you need to set the owner. First, create the database. mysql> create database  appname ; then create the  user who will have write permissions: mysql> create user ' appname '@' localhost ' identified  with mysql_native_password  by ' somepassword '; or            mysql> create user ' appname '@' localhost ' identified  with  caching_sha2_password  by ' somepassword '; mysql> flush privileges; If you forget the password, you can change it again like so: mysql> alter user ' appname'@'localhost'  identified  with mysql_native_password  by ' somepassword '; If you are using mysql version 8 or later, you need "with mysql_native_password" otherwise wordpress doesn't authenticate the database owner. At least, that was still true at the time of writing this. If you are using mysql version 5 or below, you can skip that. 

 Login to mysql and run this, where "database" is your database, "username" is your database username, and "password" is your preferred password. grant all on database.* to 'username'@'localhost' identified with mysql_native_password by "password"; Note this is for mysql 8 and above, mysql 5 and below you remove the "with mysql_native_password" part.

   https://downloads.mysql.com/archives/community/

 If you find it a nuisance to constantly type the mysql password while working with mysql, you can tell it to save, and thereafter it won't ask for it. WARNING. This exposes your database to hackers in that if they gain access to your account, your copy of mysql is also vulneralble. The command is: mysql_config_editor set --host=localhost --user=<whoever> --password

 The purpose of this blog is to save useful tips about mysql