Configurator:
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
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:
Create 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
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/
SELECT VERSION();
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"
SHOW GRANTS FOR CURRENT_USER; #MySQL 8
SHOW GRANTS FOR CURRENT_USER(); #MySQL 8
show grants\G
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;
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)
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;
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;
Set up Replication with Global Transaction ID (GTID):
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:
Activate/ deactivate “MASTER_AUTO_POSITION”:
CHANGE MASTER TO MASTER_AUTO_POSITION = 1; CHANGE MASTER TO MASTER_AUTO_POSITION = 0;
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/
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.
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
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;