John's MySQL blog

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/

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

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

mysqld --skip-grant-tables or sudo mysqld_safe --skip-grant-tables then mysql -u root mysql> FLUSH PRIVILEGES; mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword'; if this complains about permissions on the mac, try  sudo chown -R _mysql:staff /usr/local/mysql-5.7.22-macos10.13-x86_64/ sudo chmod -R 777 /usr/local/mysql-5.7.22-macos10.13-x86_64/data/ then  /usr/local/mysql/bin/mysqld_safe --skip-grant-tables or mysqld --skip-grant-tables if that doesn't work then try an init file: mysqld -- init-file =/var/lib/mysql/reset.txt  --user=mysql inside that reset.txt file put the "alter user" statement above. if that doesn't work then you have to really reset everything: warning, this WILL delete all your databases unless you back them up first. The commands below contain a backup. mysqldump -u root -p"myrootpassword"  mydatabase > /root/mydatabase.sql # make sure we move existing dbs out the way so they are safe mv /v

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