MySQL

From Anthony Pastor Wiki Notes - Verba volant, scripta manent
Jump to navigation Jump to search

Global

  • List MySQL tables engines for a MYDB database:
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'MYDB' AND ENGINE IS NOT NULL ORDER BY ENGINE;


  • Change engine for a MYTABLE table:
ALTER TABLE MYTABLE ENGINE=InnoDB;
ALTER TABLE MYTABLE ENGINE=MyISAM;


  • Unlock replication (ex: Duplicate Key):
STOP SLAVE; SET GLOBAL sql_slave_skip_counter = 1; START SLAVE;

GTID Specific

Enable GTIDs on a MySQL server

Ensure you have these parameters: 

# Replication and GTIDs
log-bin=mysql-bin
log-bin-index=mysql-bin.index
gtid-mode=ON
log-slave-updates
enforce-gtid-consistency

Create a full dump containing GTIDs

mysqldump --all-databases --single-transaction --triggers --routines --events --flush-privileges --flush-logs --hex-blob > $(hostname).sql

--single-transaction

 This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

This option is not supported for MySQL Cluster tables; the results cannot be guaranteed to be consistent due to the fact that the NDBCLUSTER storage engine supports only the READ_COMMITTED transaction isolation level. You should always use NDB backup and restore instead. 


--triggers

Include triggers for each dumped table in the output. This option is enabled by default;


 --routines, -R

Include stored routines (procedures and functions) for the dumped databases in the output. Use of this option requires the SELECT privilege for the mysql.proc table. The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to re-create the routines. However, these statements do not include attributes such as the routine creation and modification timestamps. This means that when the routines are reloaded, they will be created with the timestamps equal to the reload time.

If you require routines to be re-created with their original timestamp attributes, do not use --routines. Instead, dump and reload the contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges for the mysql database.

This option was added in MySQL 5.1.2. Before that, stored routines are not dumped. Routine DEFINER values are not dumped until MySQL 5.1.8. This means that before 5.1.8, when routines are reloaded, they will be created with the definer set to the reloading user. If you require routines to be re-created with their original definer, dump and load the contents of the mysql.proc table directly as described earlier.

Prior to MySQL 5.1.62, this option had no effect when used together with the --xml option. (Bug #11760384, Bug #52792) 


--events, -E

Include Event Scheduler events for the dumped databases in the output. This option requires the EVENT privileges for those databases. This option was added in MySQL 5.1.8. 


--hex-blob

Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263). The affected data types are BINARY, VARBINARY, the BLOB types, and BIT. 


--flush-privileges

Add a FLUSH PRIVILEGES statement to the dump output after dumping the mysql database. This option should be used any time the dump contains the mysql database and any other database that depends on the data in the mysql database for proper restoration.


  • Tiny tip to improve scp performance during copy from MASTER to future SLAVE:
scp -oCipher=arcfour dump.sql mysql_slave:/path/


  • To initialize a new SLAVE you have to create a full dump containing GTID's on an up-to-date server, an compare the value of GTID_PURGED from the dump and restored on the new slave:
head -50 dump.sql | grep -i purged -C 10

Ex: 

 --
 -- GTID state at the beginning of the backup
 --

 SET @@GLOBAL.GTID_PURGED='0d02c436-e1f1-11e4-8985-0cc47a45a200:1-3884,
 706f9866-ef16-11e4-9f3e-0cc47a4797ca:1-51299631,
 7e769578-d0ca-11e5-9f04-28924a334c0a:1-3,
 9e188f2b-e2be-11e4-8ec1-0cc47a052caa:1-138285227,
 a93c86d3-7997-11e5-a668-e83935edb232:1-19429,
 b0ab4ef2-7997-11e5-a668-441ea13b409e:1-8016902,
 c8548881-f241-11e4-b3e8-0cc47a4d6396:1-34361';

After importing and before starting replication (start slave;) you should check ALL gtid_purged have been 'imported' and are the correct one.


  • To import the dump.sql on the SLAVE:
mysql < dump.sql


  • To check 'imported' GTID's:
show global variables like '%gtid%';

In previous example you should have: 

 706f9866-ef16-11e4-9f3e-0cc47a4797ca:1-51299631,
 7e769578-d0ca-11e5-9f04-28924a334c0a:1-3,
 9e188f2b-e2be-11e4-8ec1-0cc47a052caa:1-138285227,
 a93c86d3-7997-11e5-a668-e83935edb232:1-19429,
 b0ab4ef2-7997-11e5-a668-441ea13b409e:1-8016902,
 c8548881-f241-11e4-b3e8-0cc47a4d6396:1-34361';


  • If you don't have correct values you could use the following workaround on the SLAVE before the next steps:
 RESET MASTER;

 SET @@GLOBAL.GTID_PURGED='0d02c436-e1f1-11e4-8985-0cc47a45a200:1-3884,
 706f9866-ef16-11e4-9f3e-0cc47a4797ca:1-51299631,
 7e769578-d0ca-11e5-9f04-28924a334c0a:1-3,
 9e188f2b-e2be-11e4-8ec1-0cc47a052caa:1-138285227,
 a93c86d3-7997-11e5-a668-e83935edb232:1-19429,
 b0ab4ef2-7997-11e5-a668-441ea13b409e:1-8016902,
 c8548881-f241-11e4-b3e8-0cc47a4d6396:1-34361';


  • When GTID's purged are OK, you could configure who's the MASTER on the SLAVE.

To change a MySQL topology (failover/failback) / create a new SLAVE, use: 

 CHANGE MASTER TO MASTER_HOST='CHANGE_WITH_MASTER_IP', MASTER_USER='REPLICATION_USER', MASTER_PASSWORD='REPLICATION_PASSWORD', MASTER_AUTO_POSITION = 1;


  • And start slave:
 START SLAVE;
 SHOW SLAVE STATUS \G

Some errors and their resolutions

  • If MySQL gtid_purged value isn't automatically restored with SOURCE command:
mysql> show global variables like 'gtid_purged';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_purged   |       |
+---------------+-------+

Use instead: 

mysql -u root -p < my_dump.sql


  • GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty

If you have this error when you try to import a dump

$ mysql -u root -p < sqldump_db1-us_with_gtids.sql
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

You have to do this step first: 

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

And then you can import without any issue.

MySQL sql_mode

  • Default configuration, recommended by MySQL (5.6) for sql_mode:
$ cat /usr/share/mysql/my-default.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


  • To list currently used sql_mode
show variables like '%sql_mode%';


  • To edit temporarily:
set global sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';

OR 

set @global.sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';


  • To edit once and for all, there might be some files to look at:
$ find / -name "*.cnf"
[...]
/usr/my.cnf
/usr/share/mysql/my-default.cnf
/usr/my-new.cnf

In the case where replication stops because of a duplicate key

Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '15383_1731_v8lgFBZ9q6gSCtjevZ2Q_419659097_0' [...]


You could use this script (Be conscious that this could lead in some cases to data misalignment between MySQL nodes): 

#!/bin/bash
# APA - 20150512

while true
do

test_error=$(mysql -e 'SHOW SLAVE STATUS \G' | grep Last_SQL_Errno | grep 1062)
if [ $? -eq 0 ]
then

echo "We still have a 1062 error"

# Saving master_uuid
master_uuid=$(mysql -e "SHOW SLAVE STATUS \G" | grep 'Master_UUID' | cut -d: -f2 | sed 's/ //')
echo "master_uuid: $master_uuid"
   
# Saving gtid_executed
ex_gtid_executed_full=$(mysql -e "show global variables like 'gtid_executed';" | tr \n '\n' | grep "$master_uuid")
echo "ex_gtid_executed_full: $ex_gtid_executed_full"

# Previous gtid (last part)
ex_gtid_executed_last_part=$(echo $ex_gtid_executed_full | cut -d- -f6)
echo "ex_gtid_executed_last_part: $ex_gtid_executed_last_part"

# New gtid (last part)
new_gtid_executed_last_part=$(($ex_gtid_executed_last_part + 1))
echo "new_gtid_executed_last_part : $new_gtid_executed_last_part"

# New gtid (full)
new_gtid_full=$(echo -n $ex_gtid_executed_full | cut -d: -f1 | tr -d '\n' && echo -n ":" && echo $new_gtid_executed_last_part)
echo "new_gtid_full : $new_gtid_full"

mysql << FINSQL
STOP SLAVE;
SET GTID_NEXT="$new_gtid_full";
BEGIN;
COMMIT;
SET GTID_NEXT="AUTOMATIC";
START SLAVE;
SHOW SLAVE STATUS\G
FINSQL

else
  echo "Nothing to do. We are good."
fi

sleep 2
done
Retrieved from "http://wiki.anthonypastor.fr/index.php?title=MySQL&oldid=244"