Mysql dba tips and tricks

Tuesday, 29 September 2015

Change master to example for mysql slave

mysql>CHANGE MASTER TO MASTER_HOST='x.x.x.x',MASTER_USER='replication_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='master-bin.0001', MASTER_LOG_POS= 04;

Wednesday, 9 September 2015

skip just one query hanging the slave using

mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>START SLAVE;

Monday, 7 September 2015

Set mysql delayed replication for mysql old backup

MySQL 5.6 is adding slave replication delay:

mysql> stop slave;

mysql> CHANGE MASTER TO MASTER_DELAY = 604800;

604800=7days in seconds

mysql> start slave;

mysql> show slave status\G;

Seconds_Behind_Master: 19
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
Master_Info_File: /data/mysqldata/master.info
SQL_Delay: 604800

SQL_Remaining_Delay: 604781

----------------------------------------------------------------------------------------
SQL_Delay=Seconds_Behind_Master+SQL_Remaining_Delay

604800=19+604781

-----------------------------------------------------------------------------------------

Enable Mysql Archive engine

How to enable archive engine

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| InnoDB | NO | Supports transactions, row-level locking, and foreign keys | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)

mysql> install plugin archive soname 'ha_archive.so';

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| InnoDB | NO | Supports transactions, row-level locking, and foreign keys | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+

Tuesday, 1 September 2015

How to Check MySQL engine type of tables?

My MySQL database contains tables using different storage engines (specifically myisam,innodb and others...).

mysql>SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'dbname';

A way to find out how many tables you have of different types:

mysql>select count(*), engine from information_schema.tables group by engine;

Monday, 31 August 2015

Run a query in MYSQL disable writing it to the binary log

The sql-log-bin session variable to turn off

mysql>SET sql_log_bin = 0;

all queries on your current session will not be sent to the binary log.This is only for the currently running session you are in.

If you want to revert binary logging back on, run:

mysql>SET sql_log_bin = 1;

Wednesday, 26 August 2015

Mysql Multi or how to run multiple mysql instance on the same server master with multiple slave

Mysql Multi or how to run multiple mysql instance on the same server master with multiple slave

Here is a sample (highly customized) file you can tune setting as per your requirement use mysql_multi daemon stop and start instances accordingly
Master Host=x.x.x.15
Slave Host=x.x.x.12
Slave Host=x.x.x.13

vi /etc/my.cnf

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root
password = xxxxx
log = /var/log/mysql_multi.log

###############################################

[mysqld12]
datadir=/data/mysqldata12
socket=/data/mysqldata12/mysql.sock
pid-file=/var/run/mysqld/mysqld12.pid
bind-address=x.x.x.12
innodb_file_per_table
port=3306
user=mysql
server-id = 2
read-only
master-host=x.x.x.15
master-port=3306
master-user=repl
master-password=yyyyyyy
master-connect-retry = 60
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log

old_passwords=1

# To start MySql w/o Replication Thread
skip-name-resolve
max_connections=1500
key_buffer_size=1024M
join_buffer_size=4M
read_buffer_size=16M
sort_buffer_size=192M
myisam_sort_buffer_size=32M
table_cache=2048
thread_cache_size=400
interactive_timeout=1800
wait_timeout=2700
connect_timeout=10
max_allowed_packet=64M
flush_time=1800
tmp_table_size=512M
ft_min_word_len = 3
tmpdir = /tmp/
long_query_time = 3
log-slow-admin-statements
slow_query_log = /var/log/mysql_slow_query_12.log
log-error = /var/log/mysql_error_12.log

###############################################
[mysqld13]
datadir=/data/mysqldata13
socket=/data/mysqldata13/mysql.sock
pid-file=/var/run/mysqld/mysqld13.pid
bind-address=x.x.x.13
port=3306
user=mysql
server-id = 3
master-host=x.x.x.15
master-port=3306
master-user=repl
master-password=yyyyyyy
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log

old_passwords=1

skip-name-resolve
max_connections=1500
key_buffer_size=1024M
join_buffer_size=4M
read_buffer_size=16M
sort_buffer_size=192M
myisam_sort_buffer_size=32M
table_cache=2048
thread_cache_size=400
interactive_timeout=1800
wait_timeout=2700
connect_timeout=10
max_allowed_packet=64M
flush_time=1800
tmp_table_size=512M
ft_min_word_len = 3
skip-innodb
tmpdir = /tmp/
long_query_time = 3
log-slow-admin-statements
slow_query_log = /var/log/mysql_slow_query_13.log
log-error = /var/log/mysql_error_13.log

#############################################

[mysqld15]
datadir=/data/mysqldata15
socket=/data/mysqldata15/mysql.sock
pid-file=/var/run/mysqld/mysqld15.pid
bind-address=x.x.x.15
port=3306
user=mysql
server-id = 1
log-bin = mysql-bin
binlog-ignore-db = "mysql"
old_passwords=1
skip-name-resolve
max_connections=1500
key_buffer_size=1024M
join_buffer_size=4M
read_buffer_size=16M
sort_buffer_size=192M
myisam_sort_buffer_size=32M
table_cache=2048
thread_cache_size=400
interactive_timeout=1800
wait_timeout=2700
connect_timeout=10
max_allowed_packet=256M
flush_time=1800
tmp_table_size=512M
ft_min_word_len = 3
skip-innodb

long_query_time = 10
log-slow-admin-statements
slow_query_log = /var/log/mysql_slow_query_15.log
log-error = /var/log/mysql_error_15.log

#######################################################

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Sunday, 23 August 2015

How to create federated table in mysql Example

Federated engine enables data to be accessed from a remote MySQL database on a local server without using replication or cluster technology.

Check fedrated engine

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

To enable the federate engine, type the following: (ha_federated.so file must be plugin directory)

install plugin federated soname 'ha_federated.so'

Add the line 'federated' to the /etc/my.cnf file like this:

[mysqld]
federated

Restart mysqld After the restart, go back in to the mysql CLI.

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

Create Fedrated table
On Server A(192.168.1.5)

CREATE TABLE `test_mysql` (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=MyISAM DEFAULT CHARSET=latin1;

On Server B (192.168.1.6)

CREATE TABLE `test_mysql`` (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE= FEDERATED CONNECTION='mysql://dbuser:password@192.168.1.5:3306/db_name/table_name;

Provide grants permission accordingly for network dbuser on Server A (192.168.1.5)

Enjoy!!

Friday, 21 August 2015

MySQL UDF on CENTOS 6 64-bit Example

You can add functions through the user-defined function (UDF) interface. User-defined functions are compiled as object files and then added to and removed from the server dynamically
Hope this article is helpful for you

Step1. Download the MYSQL UDF

wget https://github.com/mysqludf/lib_mysqludf_sys/archive/master.zip

unzip master.zip

cd lib_mysqludf_sys-master/

gcc -Wall -m64 -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib64/mysql/plugin/lib_mysqludf_sys.so -fPIC

Step2. Configure plugin dir in /etc/my.cnf
...
[mysqld]
plugin_dir=/var/lib/mysql/plugin
...

NOTES: Do not forget to change directory owner to mysql   
chown mysql: mysql -R /var/lib/mysql/plugin 

Step3. Copy library to plugin dir
 cp /usr/lib/lib_mysqludf_sys.so /var/lib/mysql/plugin

Step4. Restart MYSQL and Execute this SQL statements 

DROP FUNCTION IF EXISTS lib_mysqludf_sys_info;
DROP FUNCTION IF EXISTS sys_get;
DROP FUNCTION IF EXISTS sys_set;
DROP FUNCTION IF EXISTS sys_exec;
DROP FUNCTION IF EXISTS sys_eval;
CREATE FUNCTION lib_mysqludf_sys_info RETURNS string SONAME 'lib_mysqludf_sys.so';
CREATE FUNCTION sys_get RETURNS string SONAME 'lib_mysqludf_sys.so';
CREATE FUNCTION sys_set RETURNS int SONAME 'lib_mysqludf_sys.so';
CREATE FUNCTION sys_exec RETURNS int SONAME 'lib_mysqludf_sys.so';
CREATE FUNCTION sys_eval RETURNS string SONAME 'lib_mysqludf_sys.so'

STEP5
TESTING:  SELECT sys_exec ('touch /var/lib/mysql/test.txt ')
You should see /var/lib/mysql/test.txt after above statement is executed.

Tune Master/Slave Replication

The option "slave_compressed_protocol", if it is set to 1, MySQL uses compression for the slave/master protocol if both the slave and the master support it. The default is 0 (no compression).
MySQL replication works with two number of threads, IO_THREAD and SQL_THREAD. IO_THREAD connects to a master, reads binary log events from the master as they come in and just copies them over to a local log file called relay log.

Here is how to implement it:

mysql> show global variables like '%slave_compressed_protocol%';

+—————————+——-+

| Variable_name | Value |

+—————————+——-+

| slave_compressed_protocol | OFF |

+—————————+——-+

1 row in set (0.00 sec)

mysql> stop slave;

Query OK, 0 rows affected (0.25 sec)

mysql> set global slave_compressed_protocol=1;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%slave_compressed_protocol%';

+—————————+——-+

| Variable_name | Value |

+—————————+——-+

| slave_compressed_protocol | ON |

+———————————–+

Upon looking into MySQL replication, I also experimented with SSH compression since the replication goes through an SSH Tunnel. I had similar success with SSH compression as well.

/.ssh/config

Compression yes

CompressionLevel 9