Mysql for nagios 迁移方案
Mysqlfor nagios 迁移方案环境：
源端：
ip：192.25.128.35
主机名：nagios
系统：CentOS
数据库： mysql 5.1.66
存储引擎：innodb 与 myisam
物理内存：16G
innode_buffer_pool：4G
数据库信息：
+++++++++++++++++++++++++++++++++++++++++++++++++
cacti：
npc_* innodb
其余： myisam
centreon：
css_color_menu options 为 myisam
其余 innodb
centreon_status:
mytemp1.MYD nagios_hostchecks.MYD nagios_servicechecks.MYD 为 myisam
其余 innodb
centreon_storage:
各一半：
# ll | grepMYD
config.MYD
cpu_util.MYD
data_bin.MYD
data_stats_daily.MYD
data_stats_monthly.MYD
data_stats_yearly.MYD
host_test1.MYD
host_test2.MYD
index_data.MYD
instance.MYD
log_action_modification.MYD
log_action.MYD
log_archive_host.MYD
log_archive_host.MYD.bak
log_archive_last_status.MYD
log_archive_service.MYD
log_archive_service.MYD.bak
log.MYD
logs.MYD
log_snmptt.MYD
metrics.MYD
mytemp1.MYD
mytemp2.MYD
mytemp3.MYD
mytemp4.MYD
mytemp5.MYD
mytemp6.MYD
mytemp.MYD
nagios_stats.MYD
service_test1.MYD
service_test2.MYD
statistics.MYD
# ll | grepibd
acknowledgements.ibd
centreon_acl.ibd
comments.ibd
customvariables.ibd
downtimes.ibd
eventhandlers.ibd
flappingstatuses.ibd
hostgroups.ibd
hosts_hostgroups.ibd
hosts_hosts_dependencies.ibd
hosts_hosts_parents.ibd
hosts.ibd
hoststateevents.ibd
instances.ibd
issues.ibd
issues_issues_parents.ibd
modules.ibd
notifications.ibd
schemaversion.ibd
servicegroups.ibd
services.ibd
services_servicegroups.ibd
services_services_dependencies.ibd
servicestateevents.ibd
+++++++++++++++++++++++++++++++++++++++++++++++++
目标：
ip:192.25.128.30
主机名：nagiosdb
系统：redhat6.4 64bit
数据库：mysql-community-5.6.22
存储引擎：innodb 与 myisam
物理内存：
注意事项：
1. 注意使用的存储引擎
2. 注意autocommit状态 （批量load数据时，应关闭autocommit）
3. innode_buffer_pool
4. 是否需要打开binlog （归档日志）
对源数据库mysqlreport报告简略分析：
Nagios数据库参数配置分析
MySQL 5.1.66 uptime 24 11:19:10 Fri Dec 26 09:54:55 2014
__ Key_________________________________________________________________
Buffer used 6.54M of 8.00M %Used: 81.75
Current 8.00M %Usage: 100.00 //使用值相当高
Write hit 28.28%
Read hit 88.68%
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
调整key_buffer_size值 大小：128M
1.单个key_buffer的大小不能超过4G，如果设置超过4G，就有可能遇到bug:
2.建议key_buffer设置为物理内存的1/4(针对MyISAM引擎)，甚至是物理内存的30%~40%，
如果key_buffer_size设置太大，系统就会频繁的换页，降低系统性能。
因为MySQL使用操作系统的缓存来缓存数据，所以我们得为系统留够足够的内存；在很多情况下数据要比索引大得多。
3.如果机器性能优越，可以设置多个key_buffer,分别让不同的key_buffer来缓存专门的索引
深入地优化key_buffer_size，
使用"show status"来查看"Key_read_requests,Key_reads, Key_write_requests 以及Key_writes"，
以调整到更适合你的应用的大小，Key_reads/Key_read_requests的大小正常情况下得小于0.01
| Key_read_requests | 5262843294 |
| Key_reads | 596037039 | Key_reads/Key_read_requests 0.1相当不合理
3.如果Handler_read_rnd太大,则你写的SQL语句里很多查询都是要扫描整个表,而没有发挥键的作用
4.如果Threads_created太大,就要增加my.cnf中thread_cache_size的值.可以用Threads_created/Connections计算cache命中率
5.如果Created_tmp_disk_tables太大,就要增加my.cnf中tmp_table_size的值,用基于内存的临时表代替基于磁盘的
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
__ Questions___________________________________________________________
Total 848.10M 401.1/s
DMS 817.00M 386.4/s %Total: 96.33 // Data manipulation statements
Com_ 29.51M 14.0/s 3.48
COM_QUIT 3.76M 1.8/s 0.44
-Unknown 2.17M 1.0/s 0.26
Slow 10 s 112 0.0/s 0.00 %DMS: 0.00 Log: OFF //慢查询少
DMS 817.00M 386.4/s 96.33
INSERT 551.05M 260.6/s 64.97 67.45
SELECT 128.04M 60.6/s 15.10 15.67
DELETE 102.56M 48.5/s 12.09 12.55
UPDATE 35.23M 16.7/s 4.15 4.31
REPLACE 109.88k 0.1/s 0.01 0.01
Com_ 29.51M 14.0/s 3.48 //COM_ 这个类别代表着所有 MySQL 所执行过的指令，越小越好
change_db 21.34M 10.1/s 2.52
set_option 3.51M 1.7/s 0.41
show_tables 2.42M 1.1/s 0.29
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
数据库写读比例大概是64:15
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
__ SELECT and Sort _____________________________________________________
Scan 14.15M 6.7/s %SELECT: 11.05
Range 9.32M 4.4/s 7.28
Full join 6.17M 2.9/s 4.82
Range check 0 0/s 0.00
Full rng join 190 0.0/s 0.00
Sort scan 3.57M 1.7/s
Sort range 261.63k 0.1/s
Sort mrg pass 63 0.0/s
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
注意Scan 与 Full Join。Scan 指的是有多少 SELECT statements 造成 MySQL 需要进行 Full Table Scan。
Full Join 的意思与 Scan 差不多，但它是适用在多个 Tables 相互 Join 在一起的情况
越小越好
进行scan占全部select的11.05% Full Join占4.82%
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
__ Table Locks _________________________________________________________
Waited 212.94k 0.1/s %Total: 0.02 //代表 MySQL 需要等待以取得 tablelock 的次数
Immediate 856.95M 405.3/s //表示 MySQL 不需要等待即可立刻取得 table lock 的次数
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
__ Tables______________________________________________________________
Open 64 of 64 %Cache: 100.00 //table_cache已使用100%
Opened 1.93M 0.9/s
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
table_cache必须增大。 目前： table_open_cache=64 调整，2000
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
__ Connections_________________________________________________________
Max used 83 of 5000 %Max: 1.66
Total 3.76M 1.8/s
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
nagios数据库连接数不多，可以调低一些 500够了
max_connections=500
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
__ Created Temp________________________________________________________
Disk table 39.99k 0.0/s
Table 6.15M 2.9/s Size: 16.0M
File 131 0.0/s
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Disktable 的值最好是三者中最小的一个。
当暂时性的数据表被建立在硬盘中，表示此数据表没有办法被放进 RAM 里面（因为 tmp_table_size 的值设得不够大）。
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
__ Threads_____________________________________________________________
Running 1 of 17
Cached 0 of 0 %Hit: 0
Created 3.76M 1.8/s
Slow 0 0/s
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
thread_cache_size 的值需要调大，实际中 thread_cache_size 为0
根据调查发现以上服务器线程缓存thread_cache_size没有进行设置，或者设置过小,
这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,
那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果
缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个
值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量
的作用。
(-->表示要调整的值) 根据物理内存设置规则如下：
1G ---> 8
2G ---> 16
3G ---> 32
>3G ---> 64
优化方法：
1、mysql> set globalthread_cache_size=64
2、编辑/etc/my.cnf 更改/添加
thread_concurrency = 64
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
__ Aborted_____________________________________________________________
Clients 160 0.0/s
Connects 10 0.0/s
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
异常连接
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
__ Bytes_______________________________________________________________
Sent 166.30G 78.7k/s
Received 558.75G 264.3k/s
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
__ InnoDB Buffer Pool __________________________________________________
Usage 2.62G of 4.00G %Used: 65.40
Read hit 100.00%
Pages
Free 90.70k %Total: 34.60 //Free指的是缓存中的总页数, 剩余的页, 占总的34.60%
Data 170.85k 65.18 %Drty: 0.19 //Data是指缓存中, 存储索引数据的页的数量
Misc 590 0.23
Latched 0.00
Reads 49.93G 23.6k/s //代表从缓存里, 总共读取了多少M的数据.
From file 3.71k 0.0/s 0.00
Ahead Rnd 26 0.0/s //表示随机预读的次数.
Ahead Sql 13 0.0/s //表示全表扫描时, sql预读的次数.
Writes 7.77G 3.7k/s //表示写入缓存的总大小
Flushes 24.43M 11.6/s //表示缓存数据更新到硬盘的大小.
Wait Free 0 0/s //表示等待可写入数据的页的次数.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
innodb_buffer_pool_size=20G
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
__ InnoDB Lock_________________________________________________________
Waits 481233 0.2/s
Current 0
Time acquiring
Total 7054915 ms
Average 14 ms
Max 11949 ms
__ InnoDB Data, Pages, Rows____________________________________________
Data
Reads 4.34k 0.0/s
Writes 555.01M 262.5/s
fsync 548.99M 259.6/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 165.72k 0.1/s
Read 5.14k 0.0/s
Written 24.43M 11.6/s
Rows
Deleted 115.26M 54.5/s
Inserted 125.63M 59.4/s
Read 17.01G 8.0k/s
Updated 196.17M 92.8/s
操作步骤
一： 目标端安装数据库mysql5.6.22
1 系统
操作系统：redhat6.4 64bit
2 安装软件
采用源码安装方式
1. mysql安装包：mysql-5.6.22.tar.gz
Mysql 下载地址：http://dev.mysql.com/downloads/
2. Cmake安装包
mysql5.5以后是通过cmake来编译的
wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz
cmake-2.8.4.tar.gz
3 删除Linux自带的Mysql
如果已经安装了Mysql其他版本，先删除
3.1 检查是否安装了MySQL
# rpm -qa | grep -i mysql
mysql-libs-5.1.52-1.el6_0.1.i686
qt-mysql-4.6.2-20.el6.i686
mysql-server-5.1.52-1.el6_0.1.i686
mysql-5.1.52-1.el6_0.1.i686
perl-DBD-MySQL-4.013-3.el6.i686
3.2 使用yum来删除MySQL
# yum -y remove mysql-libs-5.1.52-1.el6_0.1.i686
逐个删除吧！
4 服务器上安装Mysql
4.1 先安装cmake
# tar -zxvf cmake-2.8.4.tar.gz
# cd cmake-2.8.4
# ./configure
# make
# make install
4.2 创建mysql的安装目录及数据存放目录
# mkdir/mysql //安装mysql
# mkdir /mysql/data //存放数据
4.3 创建mysql用户及用户组
# groupadd mysql
# useradd mysql -g mysql
赋予数据存放目录权限：
chown mysql:mysql -R /mysql
4.4 编译安装mysql
编译
# tar -zxvf mysql-5.6.22.tar.gz
# cd mysql-5.6.22
#cmake . -DCMAKE_INSTALL_PREFIX=/mysql-DMYSQL_DATADIR=/mysql/data -DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all-DENABLED_LOCAL_INFILE=1
参数说明：
CMAKE_INSTALL_PREFIX ： MySQL安装目录
SYSCONFDIR ： 配置文件目录
MYSQL_DATADIR :数据库目录
MYSQL_TCP_PORT ：数据库端口
MYSQL_UNIX_ADDR ：安排个目录放mysql.sock文件把，可以设置为日志存放，data存放目录等位置
WITH_EXTRA_CHARSETS : 字符
WITH_SSL ：the type ofSSL
WITH_EMBEDDED_SERVER ：Whether to build embedded server (默认：OFF)
ENABLED_LOCAL_INFILE ：Whether to enable LOCAL for LOAD DATA INFILE（默认:OFF） 允许从本地导入数据
WITH_INNOBASE_STORAGE_ENGINE: 1
参数详细见：http://dev.mysql.com/doc/refman/5.6/en/source-configuration-options.html
如果发生错误查看：
。。。/mysql-5.6.15/CMakeFiles下的CMakeError.log、CMakeOutput.log文件
注意事项：
重新编译时，需要清除旧的对象文件和缓存信息。
# rm -f CMakeCache.txt
错误：Curses library not found. Please install appropriate package
解决办法:
make clean
rm -rf /etc/my.cnf
rm -f CMakeCache.txt
yum -y install ncurses-devel
安装
# make
# make install
4.5 初始化数据库
# cd /mysql
# mkdir etc
# mkdir log
# chown -R mysql:mysql .
# cd /mysql/scripts
# ./mysql_install_db --user=mysql --basedir=/mysql/--datadir=/mysql/data/
Installing MySQL system tables...2014-12-23 11:26:21 0 TIMESTAMP withimplicit DEFAULT value is deprecated. Please use--explicit_defaults_for_timestamp server option (see documentation for moredetails).
2014-12-23 11:26:21 24207 InnoDB: Using atomics to ref count buffer poolpages
2014-12-23 11:26:21 24207 InnoDB: The InnoDB memory heap is disabled
2014-12-23 11:26:21 24207 InnoDB: Mutexes and rw_locks use GCC atomicbuiltins
2014-12-23 11:26:21 24207 InnoDB: Memory barrier is not used
2014-12-23 11:26:21 24207 InnoDB: Compressed tables use zlib 1.2.3
2014-12-23 11:26:21 24207 InnoDB: Using Linux native AIO
2014-12-23 11:26:21 24207 InnoDB: Using CPU crc32 instructions
2014-12-23 11:26:21 24207 InnoDB: Initializing buffer pool,> 2014-12-23 11:26:21 24207 InnoDB: Completed initialization of bufferpool
2014-12-23 11:26:21 24207 InnoDB: The first specified data file./ibdata1 did not exist: a new database to be created!
2014-12-23 11:26:21 24207 InnoDB: Setting file ./ibdata1> 2014-12-23 11:26:21 24207 InnoDB: Database physically writes the filefull: wait...
2014-12-23 11:26:21 24207 InnoDB: Setting log file ./ib_logfile101>
2014-12-23 11:26:21 24207 InnoDB: Setting log file ./ib_logfile1> 2014-12-23 11:26:22 24207 InnoDB: Renaming log file ./ib_logfile101 to./ib_logfile0
2014-12-23 11:26:22 24207 InnoDB: New log files created, LSN=45781
2014-12-23 11:26:22 24207 InnoDB: Doublewrite buffer not found: creatingnew
2014-12-23 11:26:22 24207 InnoDB: Doublewrite buffer created
2014-12-23 11:26:22 24207 InnoDB: 128 rollback segment(s) are active.
2014-12-23 11:26:22 24207 InnoDB: Creating foreign key constraintsystem tables.
2014-12-23 11:26:22 24207 InnoDB: Foreign key constraint system tablescreated
2014-12-23 11:26:22 24207 InnoDB: Creating tablespace and datafilesystem tables.
2014-12-23 11:26:22 24207 InnoDB: Tablespace and datafile system tablescreated.
2014-12-23 11:26:22 24207 InnoDB: Waiting for purge to start
2014-12-23 11:26:22 24207 InnoDB: 5.6.22 started; log sequence number 0
2014-12-23 11:26:25 24207 Binlog end
2014-12-23 11:26:25 24207 InnoDB: FTS optimize thread exiting.
2014-12-23 11:26:25 24207 InnoDB: Starting shutdown...
2014-12-23 11:26:26 24207 InnoDB: Shutdown completed; log sequencenumber 1625977
OK
Filling help tables...2014-12-23 11:26:26 0 TIMESTAMP with implicitDEFAULT value is deprecated. Please use --explicit_defaults_for_timestampserver option (see documentation for more details).
2014-12-23 11:26:26 24229 InnoDB: Using atomics to ref count buffer poolpages
2014-12-23 11:26:26 24229 InnoDB: The InnoDB memory heap is disabled
2014-12-23 11:26:26 24229 InnoDB: Mutexes and rw_locks use GCC atomicbuiltins
2014-12-23 11:26:26 24229 InnoDB: Memory barrier is not used
2014-12-23 11:26:26 24229 InnoDB: Compressed tables use zlib 1.2.3
2014-12-23 11:26:26 24229 InnoDB: Using Linux native AIO
2014-12-23 11:26:26 24229 InnoDB: Using CPU crc32 instructions
2014-12-23 11:26:26 24229 InnoDB: Initializing buffer pool,> 2014-12-23 11:26:26 24229 InnoDB: Completed initialization of bufferpool
2014-12-23 11:26:26 24229 InnoDB: Highest supported file format isBarracuda.
2014-12-23 11:26:26 24229 InnoDB: 128 rollback segment(s) are active.
2014-12-23 11:26:26 24229 InnoDB: Waiting for purge to start
2014-12-23 11:26:26 24229 InnoDB: 5.6.22 started; log sequence number1625977
2014-12-23 11:26:27 24229 Binlog end
2014-12-23 11:26:27 24229 InnoDB: FTS optimize thread exiting.
2014-12-23 11:26:27 24229 InnoDB: Starting shutdown...
2014-12-23 11:26:28 24229 InnoDB: Shutdown completed; log sequencenumber 1625987
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/mysql//bin/mysqladmin -u root password 'new-password'
/mysql//bin/mysqladmin -u root -h test password 'new-password'
Alternatively you can run:
/mysql//bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; /mysql//bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
New default config file was created as /mysql//my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings
#
#
#
#
# cd /mysql
# chown -R root .
# chown -R mysql data
# bin/mysqld_safe --user=mysql &
# bin/mysqld_safe --user=mysql &
24329
# 141223 11:30:32 mysqld_safe Logging to'/mysql/data/test.err'.
141223 11:30:32 mysqld_safe Starting mysqld daemon with databases from/mysql/data
4.6 配置数据库
修改密码：
# bin/mysqladmin -u root password 'mysql'
Warning: Using a password on the command line interface can be insecure.
# ps -ef | grep mysql
root 24329 23025 0 11:30 pts/0 00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql 24426 24329 0 11:30 pts/0 00:00:00 /mysql/bin/mysqld --basedir=/mysql --datadir=/mysql/data--plugin-dir=/mysql/lib/plugin --user=mysql --log-error=/mysql/data/testdb1.err--pid-file=/mysql/data/testdb1.pid
root 24496 23025 0 11:32 pts/0 00:00:00 grep mysql
# bin/mysql -u root -pmysql
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection> Server version: 5.6.22 Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
# cp support-files/mysql.server /etc/init.d/
# cp support-files/my-default.cnf etc/my.cnf
编辑etc/my.cnf,zai 下增加lower_case_table_names=1
设置环境：
# cd /etc
# vi profile
在文件最后增加：
PATH=/mysql/bin:/mysql/lib:$PATH
export PATH
生效环境变量：
# source profile
4.7 手工启动数据库
# service mysql.server status
SUCCESS! MySQL running (24426)
# service mysql.server stop
Shutting down MySQL..141223 11:37:29 mysqld_safe mysqld from pid file/mysql/data/testdb1.pid ended
SUCCESS!
+ Done bin/mysqld_safe --user=mysql (wd: /mysql)
(wd now: /etc)
# service mysql.server start
Starting MySQL. SUCCESS!
#
4.8 设置自动启动
# chkconfig mysql.server on
# chkconfig | grep mysql
至此数据库安装完成！！
二 : 源端备份数据库
#mysqldump -u root-p123456 cacti > /tmp/cacti.sql
#mysqldump -u root-p123456 centreon >/tmp/centreon.sql
#mysqldump -u root-p123456 centreon_status >/tmp/centreon_status.sql
#mysqldump -u root-p123456 centreon_storage >/tmp/centreon_storage.sql
（是否使用压缩参数等。。。）
三： 把源端备份文件拷贝到目标端
#scp /tmp/cacti.sql 172.25.128.47:/tmp
#scp /tmp/ centreon.sql 172.25.128.47:/tmp
#scp /tmp/ centreon_status.sql 172.25.128.47:/tmp
#scp /tmp/ centreon_storage.sql 172.25.128.47:/tmp
四：源端导入数据库
mysql>create database cacti;
mysql>source /tmp/cacti.sql;
检查是否全部表都导入成功：
查看当前目录下文件的个数
ls -l | grep "^-" | wc -l
查看当前目录下文件的个数，包括子目录里的
ls -lR| grep "^-" | wc -l
查看某目录下文件夹(目录)的个数，包括子目录里的
ls -lR| grep "^d" | wc -l
五：源端赋予节点访问数据库权限
mysql> select Host, User, Password fromuser;
mysql> grant all privileges ON *.* tocentreon@'172.25.128.35'>
mysql> grant all privileges ON centreon_status.*to centreon@'172.25.128.40'>
mysql> grant all privileges ON centreon_status.*to centreon@'172.25.128.42'>
mysql> grant all privileges ON centreon_status.*to centreon@'172.25.128.43'>
mysql> grant all privileges ON centreon_status.*to centreon@'172.25.128.45'>
mysql> flush privileges;
mysql> select Host, User, Password fromuser;
六：修改centreon配置文件，配置目标数据库
# cd/usr/local/centreon/etc
centreon.conf.php
conf.pm
七： 修改ndo2db数据库访问配置文件（可能需要修改数据库内的配置文件参数）
直接在centreon修改
八：RRD数据库暂时留在源库，不做操作
# du -sh status
11G status
# du -sh metrics/
36G metrics/
# pwd
/usr/local/centreon/var/lib
#
九：参数文件配置
源 参数文件：
# cat /etc/my.cnf
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
innodb_file_per_table = 1
# Disabling symbolic-links is recommendedto prevent assorted security risks
symbolic-links=0
innodb_buffer_pool_size=4G
max_connections=5000
#max_used_connections=500
max_allowed_packet = 16M
#log-slow-queries =/var/lib/mysql/mysqld-slow.log
#long_query_time = 1
myisam_sort_buffer_size = 512M
#
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
目标 参数文件：
# cat /etc/my.cnf
log_bin=mysql_bin //是否启用二进制日志，可以考虑开，也可以不开
datadir=/mysql/data
socket=/tmp/mysql.sock
#default-storage-engine=INNODB //修改默认存储引擎为innodb
user=mysql
innodb_file_per_table = 1 //是否为每个table使用单独的数据文件保存
symbolic-links=0 //支持符号链接(symbolic link)，即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录。
//要支持符号链接，需要在配置中设置symbolic-links=1(较新的版本为默认开启)
innodb_buffer_pool_size=20G //改到物理内存的80%
max_connections=500 //这个值应该调小些，也可以不调
max_allowed_packet = 16M //限制server接受的数据包大小
#log-slow-queries =/var/lib/mysql/mysqld-slow.log
#long_query_time = 1
myisam_sort_buffer_size = 512M //MyISAM表发生变化时重新排序所需的缓冲
key_buffer_size=128M //
thread_concurrency = 64 //thread_cache_size
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
页:
[1]