Jephe Wu - http://linuxtechres.blogspot.com
Environment: Percona server 5.5 running on both server db01 and db02, full backup folder on db02 is /data/fullbackup, incremental backup folder on db02 is /data/incremental, mysql datadir on both server is /srv/mysql/data
This post is applicable to any MySQL, MariaDB database with innodb engine, not only Percona server .
Objective: online backup innodb database from db1 to db2 by Percona innobackupex
Concept:
Daily backup is incremental backup. On every Wednesday, we will apply incremental backup into mysql database on db02.
Steps
1. initial full backup on db01 and apply logs
We will do a full backup on db01 then apply transaction logs to make it consistent:
# innobackupex --user=root --password=password /data/fullbackup --slave-info --no-timestamp --parallel=4
# innobackupex --apply-log --redo-only --use-memory=4G /data/fullbackup
2. Copy whole directory /data/fullbackup to db2
# cd /data/fullbackup
# ssh db02 'rm -fr /data/fullbacukp/*'
# tar cpf - . | ssh db02 'cd /data/fullbackup; tar xvpf - '
3. copy back to datadir on db02
ssh into db2 as root, assume mysql data directory on db02 is /srv/mysql/data, ssh into db02 to run commands below which performs the restoration of a backup to database server's datadir
# rm -fr /srv/mysql/data/*
# innobackupex --copy-back /data/fullbackup
# chown mysql:mysql -R /srv/mysql/data'
Now you can start up mysql from db02.
4. incremental backup done by cronjob script daily
[root@db01 cron.d]# more /root/bin/innobackupex.sh
#!/bin/sh
# do incremental backup with stream on db01 daily as follows
LSN=`ssh db02 'grep to_lsn /data/fullbackup/xtrabackup_checkpoints' | cut -d ' ' -f 3`
LOG=/var/log/innobackupex.log
(innobackupex --user=root --password=password --use-memory=4G --incremental --incremental-lsn=$LSN --stream=xbstream ./ | ssh root@db02 "cat - | xbstream -x -v -C /data/incremental" ) > $LOG 2>&1
# apply incremental log with --redo-only on db02
ssh db02 'innobackupex --apply-log --redo-only --use-memory=4G /data/fullbackup --incremental-dir=/data/incremental' >> $LOG 2>&1
# clean up
ssh db02 'rm -fr /data/incremental/*'
# actual restore on db02, only run on every Wednesday
DAY=`date +%w`
if [ $DAY -eq 3 ];then
ssh db02 '/etc/init.d/mysql stop; sync; sleep 10'
ssh db02 'rm -fr /srv/mysql/data/*;innobackupex --copy-back /data/fullbackup;chown mysql:mysql -R /srv/mysql/data'
# Note: you can use --move-back instead of above --copy-back if you are confident
ssh db02 '/etc/init.d/mysql restart'
mutt -s "db01 incremental innobackpuex - `tail -1 $LOG`" jephewu@gmail.com < $LOG
fi
5. setup cronjob on db01 to run from Mon-Fri on 8am daily
[root@db01 cron.d]# more /etc/cron.d/innobackupex
0 8 * * 1-5 root /root/bin/innobackupex.sh >/dev/null 2>&1
6. References
http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_script.html
How to use innobackupex to online backup innodb database for Percona 5.5 or MariaDB 10.0
How to upgrade Mysql 5.1 to MariaDB 10.0.X under CentOS 6.5 without using mysqldump
Jephe Wu - http://linuxtechres.blogspot.com
Environment: CentOs 6.5 64bit with default mysql 5.1 server and innodb engine for database.
Objective: Upgrade database to MariaDB 10 without dumping table contents.
Concept: Tried to upgrade directly from Mysql 5.1 to MariaDB 10 but it doesn't work, we got errors in mysql log file. However, upgrade to Percona server 5.5 first then upgrade to MariaDB 10 works.
Steps
upgrade to Percona 5.5 server first
run the following commands to upgrade mysql 5.1 to Percona 5.5,
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
rpm -e mysql mysql-server
yum install Percona-Server-client-55 Percona-Server-server-55
upgrade Percona 5.5 to MariaDB 10.0.X
vi /etc/yum.repos.d/MariaDb.repo and put into following
# MariaDB 10.0 CentOS repository list - created 2014-12-10 08:39 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1sudo yum install MariaDB-server MariaDB-client
/etc/init.d/mysql start
Keep both Zabbix 1.8 and 2.2 running for real world testing
Objective: keep both Zabbix 1.8 and 2.2 system running for same monitored hosts for real world testing.
Challenge: make zabbix 2.2 proxies to be able to monitor same set of agent hosts without adding new proxies into /etc/zabbix/zabbix_agentd.conf
Environment: CentOS 6.4 64bit, Zabbix 1.8(old system) and Zabbix 2.2(new system), Percona server 5.5(both old and new), Zabbix proxy 1.8(old system) and Zabbix proxy 2.2(new system)
Zabbix monitoring network is at 192.168.0.0/24, monitored hosts are at different network, we use about 10 zabbix proxies for monitoring. All agent hosts are configured to use these 10 zabbix proxies for monitoring.
Network diagram
.png)
Steps:
1. Preparing Zabbix 2.2 system server/VM first
We can prepare Zabbix 2.2 web server VM, zabbix 2.2 server VM and all zabbix 2.2 Proxies first.
2. Online clone Percona server 5.5 db01 to db02
refer to http://linuxtechres.blogspot.com.au/2014/02/preparing-mysql-slave-database-by.html
3. Enable ip_forward and iptables NAT on existing proxies
For proxy servers prox01,prox02,....prox10, run:
echo 1 > /proc/sys/net/ipv4/ip_forward
iptables -t nat -A POSTROUTING -p tcp -o eth0 -j SNAT --to 192.168.0.100
Note: this will enable ip forward for any traffic received from eth0, then the destination is not for prox01 itself
4. on new proxies, enable default gateway to old proxies
on each proxy server 2.2, change default gateway to coresponding old 1.8 proxy server IP address
5. on each new proxy server, configure hostname as old proxy name
e.g. still use hostname=prox01 on prox01new
This way, new proxy will get data through old proxy server iptables IP Masquerading.
6. go to new Zabbix 2.2 web GUI to modify SMTP server setting to avoid sending double alert together with old 1.8 Zabbix system.
How to do migration to 2.2 after testing
1. shutdown each proxy, bring up new coresponding proxy with same IP address
2. remove default gateway, use normal default gateway instead.
Preparing mysql slave database by online cloning Percona master database server
Jephe Wu - http://linuxtechres.blogspot.com
Objective: use Xtrabackup from Percona server 5.5 to online create slave database from master.
Environment: CentOS 6.4 64bit, Percona server 5.5 for both master(192.168.0.1) and slave(192.168.0.2), almost same hardware specs for master and slave
Steps:
1. Backup from master
# innobackupex --user=root --password=password /data/cloneforslave/ --no-timestamp
(innobackupex will create /data/cloneforslave folder automatically)
# xtrabackup --prepare --target-dir=/data/cloneforslave/
# cd /data/cloneforslave
# more xtrabackup_binlog_info
mysql-bin.129290 14072915
2. transfer to slave
#rsync --progress -avz /data/cloneforslave/ slaveserver:/data/cloneforslave --delete
(create /data/cloneforslave directory first on slaveserver)
3. Prepareing slave
cd /var/lib/mysql/
mv data data.old
cp /data/cloneforhmspzdb05 /var/lib/mysql/data -va
chown mysql:mysql -R data
vi /etc/my.cnf # to change server_id from 1 to 2, optional you can add read-only parameter
/etc/init.d/mysql start
4. setting up slave
mysql -uroot -ppassword zabbix
mysql> stop slave;
mysql> reset slave;
mysql> change master to master_host='192.168.0.1', master_user='replication', master_password='replication_password', master_log_file='mysql-bin.129290', master_log_pos=14072915;
mysql> start slave
mysql> show slave status\G
5.References
http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/setting_up_replication.html
mysql tuning tool: tuning-primer.sh
Understanding and configuring Percona server 5.5 with high server performance
Jephe Wu - http://linuxtechres.blogspot.com
Objective: understanding and configuring Percona server 5.5 with high server performance
Environment: CentOS 6.3 64bit, Percona server 5.5
Part I: the most important parameters for tuning mysql server performance
• innodb_buffer_pool_size
• innodb_log_file_sized
1. for innodb_buffer_pool_size
size it properly:
-----------------
You probably should set it at least 80% of your total memory the server can see.
Actually, if your server is dedicated mysql server, without nightly backup job scheduled, you might want to set it in such way it only left 4G to
Linux OS.
Monitoring for usage:
--------------------
Innodb_buffer_pool_pages_total - The total size of the buffer pool, in pages.
Innodb_buffer_pool_pages_dirty/ Innodb_buffer_pool_pages_total - the percentage of dirty pages
Innodb_buffer_pool_pages_data - The number of pages containing data (dirty or clean).
buffer pool page size: - always 16k per page
---------------------
[root@db03 ~]# echo "20000*1024/16" |bc
1280000
[root@db03 ~]# grep innodb_buffer_pool_size /etc/my.cnf
innodb_buffer_pool_size=20000M
---------------------
When innodb flush pages to disk:
-----------------------------
a. LRU list to flush
If there's no free pages to hold the data read from disk, innodb will LRU list to flush least recently used page.
b. Fust list
used if the percentage of dirty pages reach innodb_max_dirty_pages_pct, innodb will write pages from buffer pool memory to disk.
c. checkpoint acitivity
When innodb log file circles, it must make sure the coresponding dirty pages have been flushed to disk already before overwritting log file content.
Refer to http://www.mysqlperformanceblog.com/2011/01/13/different-flavors-of-innodb-flushing/
Monitoring flush pages:
[root@db04 ~]# mysql -uroot -ppassword -e 'show global status' | grep -i Innodb_buffer_pool_pages
Innodb_buffer_pool_pages_data 919597
Innodb_buffer_pool_pages_dirty 6686
Innodb_buffer_pool_pages_flushed 115285384
Innodb_buffer_pool_pages_LRU_flushed 0
Innodb_buffer_pool_pages_free 4321306
Innodb_buffer_pool_pages_made_not_young 0
Innodb_buffer_pool_pages_made_young 11000
Innodb_buffer_pool_pages_misc 1976
Innodb_buffer_pool_pages_old 339440
Innodb_buffer_pool_pages_total 5242879
How innodb flush dirty pages to disk:
------------------------------------
Innodb uses background thread to merge writes together to make it as sequential write, so it can improve performance.
It's called lazy flush. Another flush is called 'furious flushing' which means it has to make sure dirty pages have been written to disk before
overwriting transaction log files. So large innodb log file size will improve performance because innodb doesn't have to write dirty page more often.
2. For innodb_log_file_size
Before writing to innodb log file, it uses innodb_log_buffer_size, range is 1M-8M, don't have to be very big unless you write a lot of huge blob records.
the log entries are not page-based. Innodb will write buffer content to log file when transaction commits.
How to size it properly, a few ways below:
-----------------------
http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
1) use method mentioned in above blog
mysql> pager grep sequence
show engine innodb status\G
--------------
mysql> pager grep sequence
PAGER set to 'grep sequence'
mysql> show engine innodb status\G select sleep(60)\Gshow engine innodb status\G
Log sequence number 9591605145344
1 row in set (0.00 sec)
1 row in set (59.99 sec)
Log sequence number 9591617510628
1 row in set (0.00 sec)
mysql> select (9591617510628-9591605145344)/1024/1024 as MB_per_min;
+-------------+
| MB_per_min |
+-------------+
| 11.79245377 |
+-------------+
1 row in set (0.00 sec)
mysql> select 11.79245377*60/2 as MB_per_hour for each file in group.
+------------------+
| MB_per_hour |
+------------------+
| 353.773613100000 |
+------------------+
1 row in set (0.00 sec)
------------------
2) use innodb_os_log_written - The number of bytes written to the log file
You can monitor this parameter for 10 seconds during peak hour time, then get value X kb/s.
use x * 1800s(half an hour for one of two log files) for log file size.
3) monitor actual file size modified time, make it at least half an hour for each log file modification time.
Part II - Other useful parameters in /etc/my.cnf for innodb
1. innodb_max_dirty_pages_pct
This is an integer in the range from 0 to 99. The default value is 75. The main thread in InnoDB tries to write pages from the buffer pool so that the
percentage of dirty (not yet written) pages will not exceed this value.
2. innodb_flush_method=O_DIRECT - eliminate Double Buffering
3. default-storage-engine=innodb
4. innodb_file_per_table
5. innodb_flush_log_at_trx_commit=2
Value Meaning
0 Write to the log and flush to disk once per second
1 Write to the log and flush to disk at each commit
2 Write to the log at each commit, but flush to disk only once per second
Note that if you do not set the value to 1, InnoDB does not guarantee ACID prop-
erties; up to about a second’s worth of the most recent transactions may be lost if a
crash occurs.
6. max_connections=512
7. max_connect_errors
# default it's 10 only, we will get error like Host X is blocked because of many connection errors; unblock with mysqladmin flush-hosts
max_connect_errors=5000
# end
Part III - monitoring mysql server performance
1. aborted_client / abort_connects
2. bytes_sent/bytes_received , the number of bytes received/sent from/to all client
3. connections , the number of connection attempts (success or not ) to mysql server
4. created_tmp_disk_tables/created_tmp_tables , created on-disk temporary tables / the total number of created temporary tables
5. innodb_log_waits - The number of times that the log buffer was too small and a wait was required for it to be flushed before continuing.
mysql -uroot -ppassword -e 'show global status' | grep -i Innodb_log_waits
6. Innodb_os_log_written
The number of bytes written to the log file.
7. Innodb_rows_deleted
The number of rows deleted from InnoDB tables.
Innodb_rows_inserted
The number of rows inserted into InnoDB tables.
Innodb_rows_read
The number of rows read from InnoDB tables.
Innodb_rows_updated
The number of rows updated in InnoDB tables.
8. Max_used_connections - The maximum number of connections that have been in use simultaneously since the server started.
[root@db03 ~]# mysql -uroot -ppassword -e 'show global status' | grep -i Max_used_connections
Max_used_connections 353
9. Open_tables
The number of tables that are open.
10. Select_full_join
The number of joins that perform table scans because they do not use indexes. If this value is not 0, you should carefully check the indexes of your
tables.
11. Select_range_check - The number of joins without keys that check for key usage after each row. If this is not 0, you should carefully check the
indexes of your tables.
[root@db03 ~]# mysql -uroot -ppassword -e 'show global status' | grep -i Select_range_check
Select_range_check 1453
12. slow_queries
[root@db03 ~]# mysql -uroot -ppassword -e 'show global status' | grep -i Slow_queries
Slow_queries 38180440
13. [root@db03 ~]# mysql -uroot -ppassword -e 'show global status' | grep -i Table_locks_waited
Table_locks_waited 304
The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance
problems, you should first optimize your queries, and then either split your table or tables or use replication.
14. thread_connected/thread_running/max_used_connection
Max_used_connections
The maximum number of connections that have been in use simultaneously since the server started.
Threads_running
The number of threads that are not sleeping.
Threads_connected
The number of currently open connections.
How to install/backup Mysql 5.5 on CentOS 6 for zabbix database
Jephe Wu - http://linuxtechres.blogspot.com
Objective: upgrade Mysql to version 5.5 on CentOS 6 for zabbix installation
Environment: CentOS 6.2 64bit
Steps:
1. install epel and remi repository from http://blog.famillecollet.com/pages/Config-en
# wget http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-7.noarch.rpm
# wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
# rpm -Uvh remi-release-6*.rpm epel-release-6*.rpm
2. install mysql and mysql-server
# yum --enablerepo=remi,remi-test install mysql mysql-server
3. make it automatic start on reboot
# service mysqld start
# chkconfig --list mysqld
# chkconfig mysqld on
4. Secure mysql
# mysql_secure_installation
answer yes to disable root login remotely, drop test database, remove anonymous users etc
5. change mysql admin password
# mysqladmin -u root password password
note: assume your use 'password' as your root password.
6. create zabbix user and database
# mysql [-h localhost] -u root -ppassword
# mysql> create database zabbix;
# mysql> create user 'zabbix'[@'1.2.3.4'] identified by 'zabbix';
# mysql> grant all on zabbix.* to zabbix[@'1.2.3.4'];
# mysql> flush privileges;
# mysql> exit;
Note: [..] is optional
7. update firewall rules for iptables
update iptables firewall rules if necessary.
8. other options
use Percona mysql 5.5 at http://www.percona.com/software/
9. backup mysql - backup database 'jephe'
#!/bin/sh
WEEK=`date +%w`
mysqldump jephe --add-drop-table --add-locks --extended-insert --single-transaction --quick -u jephe -ppassword | bzip2 > /data/backup/jephe_mysql.sql.$WEEK.bz2
Posts
