[[http://tmade.de|Home tmade.de]] [[http://wiki.tmade.de|Home Wiki]] ===== MySQL ===== Configurator: http://www.mysqlcalculator.com/ https://tools.percona.com/ http://www.fromdual.com/de/mysql-configuration-file-sample ==== 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 ==== 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 ==== 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 ==== 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; ==== 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 === http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html ==== 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; ==== General Links ==== http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/ http://www.thegeekstuff.com/2010/09/install-phpmyadmin/