Zabbix实战-简易教程（3）--DB安装和表分区
一、DB安装环境
| 主机角色 | 主机IP | VIP | 操作系统版本 | 软件版本 |
| DB Master A | 192.168.1.97 |
（主从） |
CentOS 6.5 64bit | mysql-5.6.21 |
| DB Slave B | 192.168.1.98 | CentOS 6.5 64bit | mysql-5.6.21 |
二、架构图
架构详见图中的数据存储层部分：http://www.cnblogs.com/skyflask/p/7499789.html
三、Mysql安装（源码）
1. 创建目录和安装依赖包
提前规划好数据库安装目录，具体如下：
安装目录：prefix dir
/usr/local/mysql/
sock目录：
/var/lib/mysql/mysql.sock
数据目录datadir
/data/ismdata/
Innodb数据目录
/data/idbdata
相关Logs目录：
/data/myqllog/slowlog/slow.log /data/mysqllog/errorlog/log-error.log /data/mysqllog/binlog/master-bin.log
创建相应目录并安装基础包：
mkdir -p /data/{ismdata,idbdata,mysqllog} yuminstall sudo gdb ethtool ntp sysstat htop ntpdate irqbalance ncftp straceltrace hal gcc g++ cmake make bison libncurses5-dev gcc-c++ ncurses-devel groupadd-g 900 mysql useraddmysql -u 900 -g mysql -s /sbin/nologin chownmysql.mysql -R /data/ismdata chownmysql.mysql -R /data/idbdata chownmysql.mysql -R /data/mysqllog
2. 解压编译安装mysql
tar zxvf mysql-5.6.21.tar.gz cd mysql-5.6.21 cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/ -DMYSQL_DATADIR=/data/ismdata/-DENABLED_LOCAL_INFILE=1 -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock-DWITH_DEBUG=0 -DMYSQL_USER=mysql -DMYSQL_TCP_PORT=3306 -DWITH_READLINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE-ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk -DENABLE_DOWNLOADS=1 make -j 4 make install
这里编辑my.cnf文件
chmod 755scripts/mysql_install_db ./scripts/mysql_install_db --user=mysql--basedir=/usr/local/mysql --datadir=/data/ismdata cp support-files/mysql.server /etc/init.d/mysqld && chmod u+x /etc/init.d/mysqld &&chkconfig --add mysqld ln -s /usr/local/mysql/bin/mysql /usr/bin/
3.设密码
use mysql grant all privileges on *.* to root@"%" identified by "EhqW{OZzdsfwe222"; update user set password= password('EhqW{OZzdsfwe222') where user='root'; flush privileges;
4.安全相关设置
#/usr/local/mysql/bin/mysql_secure_installation
1 NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! 2 In order to log into MySQL to secure it, we'll need the current password for the root user. If you've just installed MySQL, and you haven't set the root password yet, the password will be blank,so you should just press enter here. 3 Enter current password for root (enter for none): 4 OK, successfully used password, moving on... 5 Setting the root password ensures that nobody can log into the MySQL 6 root user without the proper authorisation. 7 You already have a root password set, so you can safely answer 'n'. 8 Change the root password? [Y/n] n #是否修改密码？ 9 ... skipping. 10 By default, a MySQL installation has an anonymous user, allowing anyone 11 to log into MySQL without having to have a user account created for 12 them. This is intended only for testing, and to make the installation 13 go a bit smoother. You should remove them before moving into a 14 production environment. 15 Remove anonymous users? [Y/n] y #是否删除匿名用户？ 16 ... Success! 17 Normally, root should only be allowed to connect from 'localhost'. This 18 ensures that someone cannot guess at the root password from the network. 19 Disallow root login remotely? [Y/n] n #禁止root远程登录？ 20 ... skipping. 21 By default, MySQL comes with a database named 'test' that anyone can 22 access. This is also intended only for testing, and should be removed 23 before moving into a production environment. 24 Remove test database and access to it? [Y/n] y #删除test数据库 25 - Dropping test database... 26 ... Success! 27 - Removing privileges on test database... 28 ... Success! 29 Reloading the privilege tables will ensure that all changes made so far 30 will take effect immediately. 31 Reload privilege tables now? [Y/n] y #重载授权表？ 32 ... Success! 33 All done! If you've completed all of the above steps, your MySQL 34 installation should now be secure. 35 Thanks for using MySQL! 36 Cleaning up...
5.mysql主从配置
Mysql从5.6版本开始，可以直接进行配置，而无需锁表复制文件了。
Master上配置：
grant replication slave on *.* torepuser@'192.168.1.%' identified by "XXXXXXX"; stop slave; reset master;
Slave上配置：
stop slave; reset slave; Change master tomaster_host='192.168.1.97',master_user='repuser',master_password='XXXXX',master_auto_position=1; start slave;
最后附上my.cnf文件
[client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] socket = /var/lib/mysql/mysql.sock port = 3306 tmpdir = /tmp/tmpmysql #character set,5.6version set utf8 character_set_server=utf8 init_connect='set names utf8' skip-character-set-client-handshake=1 #deafault engine default_storage_engine = InnoDB #log info slow_query_log = 1 slow_query_log_file = /data/mysqllog/slowlog/slowquery.log long_query_time = 2 log-error = /data/mysqllog/errorlog/log-error.log skip-external-locking log_warnings back_log = 300 # fine tuning skip-name-resolve max_connections = 5000 max_allowed_packet = 32M max_heap_table_size = 128M key_buffer_size = 128M sort_buffer_size = 16M join_buffer_size = 16M net_buffer_length = 8K read_buffer_size = 128M read_rnd_buffer_size = 128M myisam_sort_buffer_size = 8M thread_cache_size = 16 #thread_concurrency = 24 table_open_cache = 4096 #mysql 5.6 new feature explicit_defaults_for_timestamp=true # * Query Cache Configuration query_cache_limit = 4M query_cache_size = 4096M query_cache_type = 1 tmp_table_size = 128M #innodb settings innodb_data_home_dir = /data/idbdata innodb_log_group_home_dir = /data/idbdata/ innodb_data_file_path = ibdata1:100M:autoextend innodb_fast_shutdown = 1 innodb_file_per_table = 1 innodb_file_io_threads = 4 innodb_open_files = 2048 innodb_buffer_pool_size = 10G #innodb_additional_mem_pool_size = 16M innodb_thread_concurrency = 16 innodb_max_dirty_pages_pct = 90 innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = 0_DIRECT innodb_lock_wait_timeout = 10 innodb_log_files_in_group = 2 innodb_log_file_size = 64M innodb_flush_log_at_trx_commit= 0 innodb_autoextend_increment = 256 innodb_buffer_pool_instances = 12 innodb_concurrency_tickets = 5000 innodb_old_blocks_time = 1000 innodb_lock_wait_timeout = 50 #log-bin settings log-bin = /data/mysqllog/binlog/master-bin.log expire_logs_days = 5 binlog_cache_size = 1M max_binlog_size = 10M [mysqldump] # Do not buffer the whole result set in memory before writing it to # file. Required for dumping very large tables quick #quote-names max_allowed_packet = 64M max_connect_errors =1000 [mysql] no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout
6、DB注意事项和要点
ａ、使用tmpfs存储临时文件
mkdir /tmp/tmpmysql 修改/etc/fstab: tmfs /tmp/tmpmysql tmpfs rw,uid=mysql,gid=mysql,size=1G,nr_inodes=10k,mode=0700 0 0 修改my.cnf tmpdir=/tmp/tmpmysql
b、设置正确的buffer pool
设置Innodb可用多少内存，建议设置成物理内存的70%~80% innodb_buffer_pool_size=10G
c、设置innodb使用O_DIRECT
这样buffer_pool中的数据就不会与系统缓存中的重复。
innodb_flush_method=O_DIRECT
d、设置合适的log大小
zabbix数据库属于写入较多的数据库，因此设置大一点可以避免MySQL持续将log文件flush到表中。
不过有一个副作用,就是启动和关闭数据库会变慢一点。
innodb_log_file_size=64M
e、打开慢查询日志
log_slow_queries=/data/mysqllog/slowquery.log
f、设置innodb_file_per_table
innodb_file_per_table = 1
四、mysqlDB分表，去housekeeping
zabbix默认会一小时执行一次housekeeping，如果数据库的量很大的时候，会让数据库变得非常慢，所以此时我们需要进行分表操作。
1、先初始化2个表（history_text和history_log），去掉主键：
Alter table history_text drop primary key, add index (id), drop index history_text_2, add index history_text_2 (itemid, id); Alter table history_log drop primary key, add index (id), drop index history_log_2, add index history_log_2 (itemid, id);
2、再进行分表操作：
/usr/local/mysql/bin/mysql -uroot -p'zabbix' zabbix < partition_call.sql /usr/local/mysql/bin/mysql -uroot -p'zabbix' zabbix < partition_all.sql
3、最后把分表操作加入计划任务，每日定时进行分表操作
1 1 * * * mysql -uzabbix -pzabbix zabbix -e "CALL partition_maintenance_all('zabbix');"
4、然后在web界面将housekeeping去掉，将数据保存周期为28天，趋势保存周期为2年：
五、参考文献和文件索引
a、分表文件：partition_call.sql和partition_all.sql位于github：
https://github.com/loveqx/zabbix-doc/tree/master/zabbix-scripts/zabbix-install
b、参考文章
