[Show page]
[Old revisions]
[[unknown link type]]
[
]

Differences

This shows you the differences between two versions of the page.

--- linux:mysql [2009/04/24 03:08]
admin
+++ linux:mysql [2019/01/12 13:42]
admin [Simple //naive// backup]
@@ Line 1: / Line 1: @@
 ======MySQL======
-=====Replication=====
+====== Backup ======
-====What replication is not====
+===== Simple //naive// backup =====
+Back up all databases into one file at once is a **bad idea**.
+  - If there is a single problem (corrupted table, unreadable file, whatever), the rest of DB will **not** be dumped
+  - 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
+<code sql>
+mysqldump -u root -p --all-databases > all_db_backup.sql
+</code>
+In a script
+<code sql>
+mysqldump -u root --password="my_secret" --all-databases > all_db_backup.sql
+</code>
+If you are going to use this simple and //not so clever// backup, please at least make the file root readable only.
+=====Better way=====
+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
+<code sql>
+GRANT LOCK TABLES, SELECT ON *.* TO 'backupuser'@'localhost' IDENTIFIED BY 'some_passwd';
+flush privileges;
+</code>
+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.
+<code bash>
+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
+</code>
+======Replication======
+=====Correcting an error=====
+Chyba:
+phpmyadmin nemohl neco neco neco
+Reseni:
+<code>
+stop slave;
+SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
+start slave;
+show slave status \G
+....: YES
+....: YES
+</code>
+=====What replication is not=====
   * 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.
@@ Line 47: / Line 105: @@
 ====== Performance ======
   * http://www.mysqlperformanceblog.com
+====== Regain root access ======
+From http://www.simplehelp.net/2008/11/26/how-to-reset-a-lost-mysql-root-password/
+<code>
+# /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
+</code>

linux/mysql.txt · Last modified: 2019/10/21 09:53 by admin