Back up all databases into one file at once is a BAD idea.

1. If there is a single problem (corrupted table, unreadable file, whatever), the rest of DB will **not* be dumped
2. Restoring one DB within many from one file is difficult, time consuming and with slightly bigger databases ~ 10GB i wish you a lot of RUM

Here is an example anyway

mysqldump -u root -p --all-databases > all_db_backup.sql

In a script

mysqldump -u root --password="my_secret" --all-databases > all_db_backup.sql

If you are going to use this simple and not so clever backup, please at least make the file root readable only.

The backup user just need lock and read privileges. Create a backup user and grant lock tables and select privileges to it with the following MariaDB/MySQL commands

GRANT LOCK TABLES, SELECT ON *.* TO 'backupuser'@'localhost' IDENTIFIED BY 'some_passwd';
FLUSH privileges;

Then list all databases and in backup one after another. Use umask to keep you files root readable only. Here is our script for 7 days rotating backups.

DAY=$(date +"%w")
BACKUP_TMP="/some_storage/db/mysql-${DAY}"
PASS="some_passwd"
USR="backupuser"
 
umask 077
mkdir -p $BACKUP_TMP
 
mysql -u $USR  --password=$PASS -Bse "SHOW DATABASES" | while read DB ; do
        mysqldump --no-tablespaces --single-transaction -u $USR --password=$PASS ${DB} | bzip2 > $BACKUP_TMP/${DB}.sql.bz2
done

Chyba:

phpmyadmin nemohl neco neco neco

Reseni:

stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;

show slave status \G
....: YES
....: YES 

• Replication is not a backup policy. A mistyped DELETE statement will be replicated on the slave too, and you could end up with two, perfectly synchronized, empty databases. Replication can help protect against hardware failure though.
• Replication is not an answer to all performance problems. Although updates on the slave are more optimized than if you ran the updates normally, if you use MyISAM tables, table-locking will still occur, and databases under high-load could still struggle.
• Replication is not a guarantee that the slave will be in sync with the master at any one point in time. Even assuming the connection is always up, a busy slave may not yet have caught up with the master, so you can't simply interchange SELECT queries across master and slave servers.

Source: http://www.databasejournal.com/features/mysql/article.php/3355201/Database-Replication-in-MySQL.htm

First import mysql tables whith all users

mysql -u root -p mysql <temp/mysql.sql 

Then you can do flush privileges or use the old password. Finaly import all databases

cd /srv/temp
for i in *; do 
   db=${i%%.sql};
   echo $db;
   mysqladmin create $db --password=xxxxx;
   mysql -u root --password=xxxxxx $db <$i;
done

unfortunately in the middle the cycle failed (i caused it intentionaly)

X=0
for i in *; do
   test $X -eq 0 && mv $i $i.done;
   if [ $i = 'gld.sql' ]; then
     X=1;
   fi;
done

mysql> CHECK TABLE fixtures;
mysql> REPAIR TABLE fixtures;
mysql> REPAIR TABLE fixtures USE_FRM;

• http://felipecruz.com/repair-mysql-database.php

• http://www.mysqlperformanceblog.com

From http://www.simplehelp.net/2008/11/26/how-to-reset-a-lost-mysql-root-password/

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root

mysql> use mysql;
mysql> update user set password=PASSWORD("NewMySQLPassword") where User="root";
mysql> flush privileges;
mysql> quit 

# /etc/init.d/mysql stop
# /etc/init.d/mysql start