This is an old revision of the document!


Home tmade.de

Home Wiki

MySQL

Backup

Example backupscript to backup each database as a single file:

#!/bin/bash

MYSQL="/usr/bin/mysql"
USERNAME="root"
PASSWORD=""
DBHOST="localhost"
DBEXCLUDE="information_schema"
BACKUPDIR="/mysql_backups"
LOGFILE=${BACKUPDIR}/`date +%d.%m.%y`.log
LOGERR=${BACKUPDIR}/ERROR_`date +%d.%m.%y`.log
HOST="`hostname`"
MAILADDR="12345@test.de 6789@test.de"

# IO redirection for logging.
touch ${LOGFILE}
exec 6>&1           # Link file descriptor #6 with stdout.
                    # Saves stdout.
exec > ${LOGFILE}     # stdout replaced with file ${LOGFILE}.

touch ${LOGERR}
exec 7>&2           # Link file descriptor #7 with stderr.
                    # Saves stderr.
exec 2> ${LOGERR}     # stderr replaced with file ${LOGERR}.
#

cd ${BACKUPDIR}

DBNAMES="`${MYSQL} --user=${USERNAME} --password=${PASSWORD} --host=${DBHOST} --batch --skip-column-names -e "show databases"| sed 's/ /%/g'`"
#echo $DBNAMES
# If DBs are excluded
        for exclude in ${DBEXCLUDE}
        do
                DBNAMES=`echo ${DBNAMES} | sed "s/\b${exclude}\b//g"`
        done
#echo $DBNAMES

for DB in ${DBNAMES}
        do
        # Prepare ${DB} for using
        DB="`echo ${DB} | sed 's/%/ /g'`"

                        echo Daily Backup of Database \( ${DB} \)
                echo
                /usr/bin/mysqldump --user=${USERNAME} --password=${PASSWORD} --host=${DBHOST} --quick --add-drop-database --single-transaction --hex-blob -B ${DB} | gzip > ${DB}_dump_`date +%d.%m.%y`.sql.gz


done

if [ -s "${LOGERR}" ]
        then
                #cat "${LOGERR}" | mail -s "ERRORS REPORTED: MySQL Backup error Log for ${HOST} - `date`" ${MAILADDR}
                cat "${LOGERR}" | mail -s "ERRORS REPORTED: MySQL Backup error Log for ${HOST} - `date`" ${MAILADDR} -r "mail@test.de"
        else
                #cat "${LOGFILE}" | mail -s "MySQL Backup Log for ${HOST} - `date`" ${MAILADDR}
                cat "${LOGFILE}" | mail -s "MySQL Backup Log for ${HOST} - `date`" ${MAILADDR} -r "mail@test.de"
fi

tar -czf `date +%A`.tgz *.gz *.log
rm *.gz *.log

Backup with Master-Info

To rebuild replication or migrate to another system:

#!/bin/sh

USERNAME="root"
BACKUPDIR="/mybackup"
DBHOST="127.0.0.1"
PORT=3306

#exclude "mysql", "information_schema" and "performance_schema" from dump:
candidates=$(echo "show databases" | mysql -h 127.0.0.1 -P 3306 -u${USERNAME} -p${PASSWORD} | grep -Ev "^(Database|mysql|information_schema|performance_schema)$")

#with or without GTID:
mysqldump --user=${USERNAME} --password --host=${DBHOST} --port=${PORT} --quick --triggers --routines --events --add-drop-database --single-transaction --master-data --hex-blob --databases $candidates | gzip > $BACKUPDIR/dump_`date +%d.%m.%y`.sql.gz

Check also:

https://www.percona.com/blog/2013/02/08/how-to-createrestore-a-slave-using-gtid-replication-in-mysql-5-6/

Restore

Ceate a file with following content and make it executable (chmod +x import_db.sh)

#!/bin/bash

i=1
for file in $(ls)
do

# mysql < $file
 gunzip < $file | mysql -uroot -pmypassword
done

Note: A file “*.sql.gz” is expected!

To start the import goto the folder where the dump-files are located and run the script within the folder (e.g. /root/import_db.sh, if import_db.sh is in /root)

To import a dump.sql file execute

mysql -uroot -p < dump.sql

Another way to import a dumpfile is to login into the mysql-shell (mysql -uroot -p) and execute

mysql> source /path/to/dump/file/*.sql

Manually Install

If you need to install a second instance or to repair mysql-db-files, it can be done with the following commands:

mysql_install_db --user=mysql --datadir=/var/lib/mysql_datadir/                      #Manually install mysql-db-files 
mysqld --initialize --datadir=/var/lib/mysql/mysql_datadir/                          #Manually install mysql-db-files (mysql 5.7)
mysqld –initialize                                                                   #Manually install mysql-db-files (mysql 5.6)
mysqld_safe --defaults-file=/etc/mysql/my.cnf &                                      #Start mysql with personalized config-file (default path of my.cnf may differ!)
mysqladmin -uroot -ppassword -h 127.0.0.1n -S /var/run/mysqld/mysqld.sock shutdown   #Stop mysql (without init-script)
mysql -uroot -ppassword -S /var/lib/mysql/mysql.sock -ppassword                      #Connect to mysql console
mysql -uroot -ppassword -P 3307                                                      #Connect to mysql console

Useful links: http://www.ducea.com/2009/01/19/running-multiple-instances-of-mysql-on-the-same-machine/

Maintain

SET PASSWORD = PASSWORD('newpassword');                                                                    #Login to mysql (mysql -uroot -p) and change password
create database mydatabase;                                                                                #Create database "mydatabase"
CREATE database if not exists mydatabase;                                                                  #Create database "mydatabase" if not existent
CREATE TABLE test (id INT,data VARCHAR(100));                                                              #Create table "test". DB to inset table has been selected.
CREATE database if not exists ssh_key_manage;
CREATE table users( username varchar(30), `key` longtext, PRIMARY KEY(username) );
CREATE table server( hostname varchar(30), ip varchar(40), PRIMARY KEY(hostname) );
CREATE table berechtigungen( username varchar(30), hostname varchar(30), PRIMARY KEY(username, hostname), FOREIGN KEY(username) REFERENCES users(username), FOREIGN KEY(hostname) REFERENCES server(hostname) );
DROP TABLE test;                                                                                           #Drop table "test". DB to delete table has been selected.
create user testuser IDENTIFIED BY 'mypassword';                                                           #Creates user "testuser" with password "mypassword"
CREATE USER 'root'@'hostname' IDENTIFIED BY 'mypassword';                                                  #Creates user "root" on hostname
UPDATE mysql.user SET Password = PASSWORD( 'mypassword' ) WHERE User = 'root' AND Host = 'sles10';         #Update acount
UPDATE user SET Password=PASSWORD('mypassword') WHERE User='root'; FLUSH PRIVILEGES;                       #Update acount for all entries (localhost, hostname, 127.0.0.1) at once
update user set host = 'hostname or IP' where user = 'username';                                           #Update acount
update user set host = '192.168.1.%' where user = 'username';                                              #Update acount
UPDATE mysql.user SET authentication_string = PASSWORD('secret') WHERE User = 'root' AND Host = 'localhost';   #Update root password for mysql 5.7, 8
UPDATE user SET authentication_string='secret' WHERE user='root@127.0.0.1';                                    #Update root password for mysql 5.7, 8
ALTER USER USER() IDENTIFIED BY 'new_password';                                                                #Update root password for mysql 5.7, 8
UPDATE server SET ip='10.6.1.33' WHERE ip='10.6.1.22';
UPDATE berechtigungen SET hostname='test01.local' WHERE hostname='test05.local';
UPDATE server SET hostname='test.local' WHERE hostname='test.de';
drop user 'testuser'@'%';                                                                                  #Delete user "testuser"
delete from mysql.db where user='username';                                                                #
drop user 'testuser'@'localhost';                                                                          #Delete user "testuser"
drop database mydatabase;                                                                                  #Delete database "mydatabase"
#drop all databases except "mysql" and "information_schema":
mysql -uroot -psecret  -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema| grep -v test | gawk '{print "drop database " $1 ";"}' | mysql -uroot -psecret
show databases;                                                                                            #Show databases
mysqlshow -h 127.0.0.1 -uroot -ptest test3                                                                 #Show database "test3" 
SHOW PROCESSLIST;                                                                                          #Show established MySQL-Connections
show global status like '%connection%';
show status like '%onn%';
show status like 'Conn%';
select user,host from mysql.user;                                                                          #Show all users
select user,password,host from mysql.user;                                                                 #Show user, password(hashkey), host for all users
select user,authentication_string,host from mysql.user;                                                    #Mysql 7, 8
select user,grant_priv,host from mysql.user;
select user,max_user_connections,host from mysql.user;
SELECT DBSCHEMA FROM test.schema WHERE NAME = 'value' INTO OUTFILE '/tmp/data.txt';                        #Pipe output into file "/tmp/data.txt"
#grants example:
GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO user@'192.168.1.xx' identified by 'mypassword';
Grant all privileges on testdb.* to test@localhost identified by 'mypassword';                             #Grant all privileges for user "test" on database "testdb"
GRANT ALL ON *.* TO 'root'@'localhost';                                                                    #MySQL 8, all grants except "grant"
GRANT ALL ON *.* to root@localhost WITH GRANT OPTION;                                                      #MySQL 8
GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;  #Grant all privileges for user "debian-sys-maint"
grant super on *.* to 'user'@'192.168.1.xx' identified by 'mypassword';                                    #
show grants for 'test'@'localhost';                                                                            #Show privileges for user "test"
show grants for 'root'@'localhost';                                                                        #Show privileges for user "root"
show grants for 'root'@'hostname';                                                                         #Show privileges for user "root"
revoke ALL PRIVILEGES on testdb.* from 'test'@'localhost';                                                 #Remove all privileges for user "test" on database "testdb"
flush privileges;                                                                                          #Update database permissions/privilages
use database;                                                                                              #Login in database (before list all db´s with "show databases", then replace database with db name)
mysql_upgrade --force --password=mypassword
show tables;                                                                                               #Show database tables (before execute "use dbname")
show columns from tablenameXY;

Variables:

SHOW VARIABLES LIKE '%log%';
SHOW VARIABLES like 'open_files_limit';
SHOW VARIABLES\G
SHOW VARIABLES like '%connections%';
SHOW VARIABLES LIKE 'validate_password%'
SHOW [STORAGE] ENGINES;
show global variables like 'log%';
SHOW VARIABLES LIKE  'char%';                                                                              #Show default character set
show variables where Variable_name ='datadir';
show variables like '%gtid%';
update user set max_user_connections=2 where user like 'username%';                                        #Update all users with names "username*"
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH MAX_USER_CONNECTIONS 5;                             #mysql 5.6
ALTER USER 'root'@'localhost' WITH MAX_USER_CONNECTIONS 5;                                                 #mysql 5.7                                        
select * from tablenameXY\G;                                                                               #Show properties of table "tablenameXY"
select TABLE_SCHEMA,TABLE_NAME,ENGINE from tables where engine!='innodb';
update tablenameXY set SERVER='192.168.1.xx',PASSWORD='mypassword';                                        #Update table "tablenameXY"
update core_config_data set value='https://www.domain.de/shop/' where path="web/unsecure/base_url";        #Update table "core_config_data"
select * from core_config_data where path="web/unsecure/base_url";                                         #Show values of table "core_config_data"
INSERT INTO sysclient (customergroup) VALUES ('ABC');                                                      #Insert into table "sysclient" the new field "costomergroup" with value "ABC"                         
DELETE FROM tablenameXY WHERE NAME = ('value');                                                            #Delete entry "§§Homepage$$"
mysql_install_db --user=mysql --datadir=/var/lib/mysql/                                                    #Manually install default database
mysql_config_editor print --login-path=root                                                                #Check entry 
mysql_config_editor set --login-path=root --host=localhost --user=root --port=3306 --password              #Note: enter password within ""!
mysql --login-path=backup                                                                                  #Login
mysqld_safe --user=mysql &                                                                                 #Start mysql manually (without init-script)
SHOW BINARY LOGS;                                                                                          #SHOW BINARY LOGS
PURGE MASTER LOGS TO 'mysql-bin.000062';                                                                   #Delete old slave-replication-log-files to e.g. "mysql-bin.000062"
purge master logs before now();                                                                            #Delete all bin-logs except the actual
for i in `seq 1 1000`; do mysql -u root --password=bad -h 127.0.0.1 2>/dev/null; done                      #Security Login-Check
describe [db_name.]table_name;                                                                             #Show table info
SET FOREIGN_KEY_CHECKS=0;                                                                                  #disable the foreign key check
SET FOREIGN_KEY_CHECKS=1;                                                                                  #enable the foreign key check

User-Limits:

use mysql
update user set max_user_connections=180 where user like 'app-user'; 
update user set max_user_connections=5 where user like 'root'; 
update user set max_user_connections=3 where user like 'nagios'; 
update user set max_user_connections=3 where user like 'replicant'; 
SET GLOBAL max_connections = 200;
flush privileges;

Master-Master Replication

Configuration

MySQL <= 5.5

Server 1:

[...]
[mysqld]
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2                    #Amount of replication nodes
auto-increment-offset = 1

master-host = 192.168.63.205
master-user = slave1_user
master-password = slave1_password
master-connect-retry = 60
replicate-do-db = exampledb                     #Set db you want to replicate. Comment it out if you want to replicate all db´s!
#replicate-ignore-db = xyz                      #Set if you want to exclude a db from replication

log-bin = /var/lib/mysql/mysql-bin.log
binlog-do-db = exampledb

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

expire_logs_days        = 10
max_binlog_size         = 500M
[...]
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.63.205
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: matisse-bin.000004
        Read_Master_Log_Pos: 258
             Relay_Log_File: localhost-relay-bin.000006
              Relay_Log_Pos: 397
      Relay_Master_Log_File: matisse-bin.000004
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: replication
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 258
            Relay_Log_Space: 397
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Create users for replication

Server 1:

GRANT REPLICATION SLAVE ON *.* TO 'slave2_user'@'%' IDENTIFIED BY 'slave2_password';
FLUSH PRIVILEGES;

Server 2:

GRANT REPLICATION SLAVE ON *.* TO 'slave1_user'@'%' IDENTIFIED BY 'slave1_password';
FLUSH PRIVILEGES;

Replication information

To find information to implement replication for server 2:

Server 1:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;                         #Note this information (file and position)
UNLOCK TABLES;
quit

Server 2:

Note: Please enter the values from the lokal “/etc/my.cnf” (IP, user, password from server 2 in this case)!

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.63.206', MASTER_USER='slave2_user', MASTER_PASSWORD='slave2_password', MASTER_LOG_FILE='mysql-bin.000028', MASTER_LOG_POS=98;
START SLAVE;
SHOW SLAVE STATUS\G;

Find information to implement replication for server 1:

Server 2:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;                         #Note this information (file and position)
UNLOCK TABLES;
quit

Server 1:

Note: Please enter the values from the lokal ”/etc/my.cnf” (IP, user, password from server 1 in this case)!

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.63.205', MASTER_USER='slave1_user', MASTER_PASSWORD='slave1_password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=196;
START SLAVE;
SHOW SLAVE STATUS\G;

MySQL >= 5.6

Set up Replication with Global Transaction ID (GTID):

  • Create a dump on productive master
  • „stop slave;“ on productive master
  • „stop slave;“ on slave you want to create or recreate
  • „reset master“ on slave
  • Check „SHOW GLOBAL VARIABLES LIKE 'gtid%';“ - there must be not entries for „gtid_executed” and “gtid_purged”
  • “CHANGE MASTER TO MASTER_HOST=“IP-Replication-Master”, MASTER_AUTO_POSITION=1;”
  • “start slave” on slave and check replication („show slave status\G“)
  • “start slave” on productive master and check replication („show slave status\G“)

Hint: If the replication hasn´t previously been established, you have to execute

CHANGE MASTER TO MASTER_HOST="IP-Replication-Master", MASTER_PORT=3306, MASTER_USER="replication", MASTER_PASSWORD="secret", MASTER_AUTO_POSITION=1

as master and credentials aren´t know yet.

See also on:

https://www.percona.com/blog/2013/02/08/how-to-createrestore-a-slave-using-gtid-replication-in-mysql-5-6/

Activate/ deactivate “MASTER_AUTO_POSITION”:

CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
CHANGE MASTER TO MASTER_AUTO_POSITION = 0;

Useful Replication Commands

SHOW BINARY LOGS;
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G;
RESET MASTER;
RESET SLAVE;
SHOW GLOBAL STATUS;
PURGE MASTER LOGS TO 'mysql-bin.000062';         #Delete old slave-replication-log-files to e.g. "mysql-bin.000062"
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';  
purge master logs before now();                  #Delete all bin-logs except the actual
show global variables like 'gtid_executed'; 
show global variables like 'gtid_purged';
show global variables like '%gtid%';

See also: http://www.howtoforge.de/howto/einrichten-von-master-master-replikation-mit-mysql-5-auf-debian-etch/

Replication problem (without GTID)

If you are going to delete a database (e.g. on server 1), which exists just on ONE replication host, it will stop the slave on the other host (server 2).

Therefore it will produce an error (on server 2) and the replication will stop with an error message (shown by execute “SHOW SLAVE STATUS\G;”).

To solve such a situation please go ahead as follows:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;            #Skip "one" problematic query (if you are aware how much queries you want to skip you can put the number of failed queries!) 
START SLAVE

or all at once:

STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; SHOW SLAVE STATUS\G;

This will skip one error query.

Replication problem (with GTID)

If a replication problem occurs (e. g. “Error 'Can't drop database 'test'; database doesn't exist' on query. Default database: 'test'. Query: 'drop database test'”)

you can repair it like:

show slave status\G

Have a look on:

Retrieved_Gtid_Set: b8abe1d3-4b15-11e9-8ac8-00505688ca57:3-5
Executed_Gtid_Set: b4298340-4b15-11e9-a24e-0050568849fb:1-7,
b8abe1d3-4b15-11e9-8ac8-00505688ca57:1-4

Then you have to execute:

stop slave;
SET GTID_NEXT='b8abe1d3-4b15-11e9-8ac8-00505688ca57:5';
BEGIN; COMMIT; SET GTID_NEXT='AUTOMATIC'; start slave; SHOW SLAVE STATUS \G

Point in Time Recovery

Reset Password

Run MySQL daemon with

mysqld_safe --skip-grant-tables &

and you are able to login and to set a new password.

See also:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-unix

MySQL 5.6 and 5.7:

/usr/sbin/mysqld --user=mysql --skip-grant-tables &

MySQL 8:

./mysqld --user=mysql --datadir=/some/path --basedir=/some/path --skip-grant-tables --skip-networking &

Afterwards you are able to login via “mysql” without password. In the mysql client, tell the server to reload the grant tables so that account-management statements work:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
FLUSH PRIVILEGES;
database/mysql.1559824893.txt.gz · Last modified: 2019/06/06 14:41 by tmade
 
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 4.0 International
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki