Mysql for 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:
各一半：
[root@sznagios centreon_storage]# ll | grep MYD
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
[root@sznagios centreon_storage]# ll | grep ibd
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 需要等待以取得 table lock 的次数
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
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Disk table 的值最好是三者中最小的一个。
当暂时性的数据表被建立在硬盘中，表示此数据表没有办法被放进 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 global thread_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
[root@test /]# 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
[root@test ~]# yum -y remove mysql-libs-5.1.52-1.el6_0.1.i686
逐个删除吧！
4 服务器上安装Mysql
4.1 先安装cmake
[root@test ]# tar -zxvf cmake-2.8.4.tar.gz
[root@test ]# cd cmake-2.8.4
[root@test ]# ./configure
[root@test ]# make
[root@test ]# make install
4.2 创建mysql的安装目录及数据存放目录
[root@test cmake-2.8.4]# mkdir /mysql //安装mysql
[root@test cmake-2.8.4]# mkdir /mysql/data //存放数据
4.3 创建mysql用户及用户组
[root@test cmake-2.8.4]# groupadd mysql
[root@test cmake-2.8.4]# useradd mysql -g mysql
赋予数据存放目录权限：
chown mysql:mysql -R /mysql
4.4 编译安装mysql
编译
[root@test tmp]# tar -zxvf mysql-5.6.22.tar.gz
[root@test tmp]# cd mysql-5.6.22
[root@test 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 of SSL
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
安装
[root@test mysql-5.6.15]# make
[root@test mysql-5.6.15]# make install
4.5 初始化数据库
[root@test mysql]# cd /mysql
[root@test mysql]# mkdir etc
[root@test mysql]# mkdir log
[root@test mysql]# chown -R mysql:mysql .
[root@test scripts]# cd /mysql/scripts
[root@test scripts]# ./mysql_install_db --user=mysql --basedir=/mysql/ --datadir=/mysql/data/
Installing MySQL system tables...2014-12-23 11:26:21 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-12-23 11:26:21 24207 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-12-23 11:26:21 24207 [Note] InnoDB: The InnoDB memory heap is disabled
2014-12-23 11:26:21 24207 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-12-23 11:26:21 24207 [Note] InnoDB: Memory barrier is not used
2014-12-23 11:26:21 24207 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-12-23 11:26:21 24207 [Note] InnoDB: Using Linux native AIO
2014-12-23 11:26:21 24207 [Note] InnoDB: Using CPU crc32 instructions
2014-12-23 11:26:21 24207 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-12-23 11:26:21 24207 [Note] InnoDB: Completed initialization of buffer pool
2014-12-23 11:26:21 24207 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2014-12-23 11:26:21 24207 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2014-12-23 11:26:21 24207 [Note] InnoDB: Database physically writes the file full: wait...
2014-12-23 11:26:21 24207 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2014-12-23 11:26:21 24207 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2014-12-23 11:26:22 24207 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2014-12-23 11:26:22 24207 [Warning] InnoDB: New log files created, LSN=45781
2014-12-23 11:26:22 24207 [Note] InnoDB: Doublewrite buffer not found: creating new
2014-12-23 11:26:22 24207 [Note] InnoDB: Doublewrite buffer created
2014-12-23 11:26:22 24207 [Note] InnoDB: 128 rollback segment(s) are active.
2014-12-23 11:26:22 24207 [Warning] InnoDB: Creating foreign key constraint system tables.
2014-12-23 11:26:22 24207 [Note] InnoDB: Foreign key constraint system tables created
2014-12-23 11:26:22 24207 [Note] InnoDB: Creating tablespace and datafile system tables.
2014-12-23 11:26:22 24207 [Note] InnoDB: Tablespace and datafile system tables created.
2014-12-23 11:26:22 24207 [Note] InnoDB: Waiting for purge to start
2014-12-23 11:26:22 24207 [Note] InnoDB: 5.6.22 started; log sequence number 0
2014-12-23 11:26:25 24207 [Note] Binlog end
2014-12-23 11:26:25 24207 [Note] InnoDB: FTS optimize thread exiting.
2014-12-23 11:26:25 24207 [Note] InnoDB: Starting shutdown...
2014-12-23 11:26:26 24207 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OK
Filling help tables...2014-12-23 11:26:26 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-12-23 11:26:26 24229 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-12-23 11:26:26 24229 [Note] InnoDB: The InnoDB memory heap is disabled
2014-12-23 11:26:26 24229 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-12-23 11:26:26 24229 [Note] InnoDB: Memory barrier is not used
2014-12-23 11:26:26 24229 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-12-23 11:26:26 24229 [Note] InnoDB: Using Linux native AIO
2014-12-23 11:26:26 24229 [Note] InnoDB: Using CPU crc32 instructions
2014-12-23 11:26:26 24229 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-12-23 11:26:26 24229 [Note] InnoDB: Completed initialization of buffer pool
2014-12-23 11:26:26 24229 [Note] InnoDB: Highest supported file format is Barracuda.
2014-12-23 11:26:26 24229 [Note] InnoDB: 128 rollback segment(s) are active.
2014-12-23 11:26:26 24229 [Note] InnoDB: Waiting for purge to start
2014-12-23 11:26:26 24229 [Note] InnoDB: 5.6.22 started; log sequence number 1625977
2014-12-23 11:26:27 24229 [Note] Binlog end
2014-12-23 11:26:27 24229 [Note] InnoDB: FTS optimize thread exiting.
2014-12-23 11:26:27 24229 [Note] InnoDB: Starting shutdown...
2014-12-23 11:26:28 24229 [Note] InnoDB: Shutdown completed; log sequence number 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
[root@test scripts]#
#
#
#
[root@test scripts]# cd /mysql
[root@test mysql]# chown -R root .
[root@test mysql]# chown -R mysql data
[root@test mysql]# bin/mysqld_safe --user=mysql &
[root@testdb1 mysql]# bin/mysqld_safe --user=mysql &
[1] 24329
[root@testdb1 mysql]# 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 配置数据库
修改密码：
[root@test mysql]# bin/mysqladmin -u root password 'mysql'
Warning: Using a password on the command line interface can be insecure.
[root@test mysql]# 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
[root@test 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 id is 3
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>
[root@test mysql]# cp support-files/mysql.server /etc/init.d/
[root@test mysql]# cp support-files/my-default.cnf etc/my.cnf
编辑etc/my.cnf,zai [mysqld]下增加lower_case_table_names=1
设置环境：
[root@test mysql]# cd /etc
[root@test etc]# vi profile
在文件最后增加：
PATH=/mysql/bin:/mysql/lib:$PATH
export PATH
生效环境变量：
[root@test etc]# source profile
4.7 手工启动数据库
[root@test etc]# service mysql.server status
SUCCESS! MySQL running (24426)
[root@test etc]# service mysql.server stop
Shutting down MySQL..141223 11:37:29 mysqld_safe mysqld from pid file /mysql/data/testdb1.pid ended
SUCCESS!
[1]+ Done bin/mysqld_safe --user=mysql (wd: /mysql)
(wd now: /etc)
[root@test etc]# service mysql.server start
Starting MySQL. SUCCESS!
[root@test etc]#
4.8 设置自动启动
[root@test mysql]# chkconfig mysql.server on
[root@test mysql]# chkconfig | grep mysql
至此数据库安装完成！！
二 : 源端备份数据库
[root@test mysql]#mysqldump -u root -p123456 cacti > /tmp/cacti.sql
[root@test mysql]#mysqldump -u root -p123456 centreon >/tmp/centreon.sql
[root@test mysql]#mysqldump -u root -p123456 centreon_status >/tmp/centreon_status.sql
[root@test mysql]#mysqldump -u root -p123456 centreon_storage >/tmp/centreon_storage.sql
（是否使用压缩参数等。。。）
三： 把源端备份文件拷贝到目标端
[root@test mysql]#scp /tmp/cacti.sql 172.25.128.47:/tmp
[root@test mysql]#scp /tmp/ centreon.sql 172.25.128.47:/tmp
[root@test mysql]#scp /tmp/ centreon_status.sql 172.25.128.47:/tmp
[root@test mysql]#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 from user;
mysql> grant all privileges ON *.* to centreon@'172.25.128.35' identified by '123456';
mysql> grant all privileges ON centreon_status.* to centreon@'172.25.128.40' identified by '123456';
mysql> grant all privileges ON centreon_status.* to centreon@'172.25.128.42' identified by '123456';
mysql> grant all privileges ON centreon_status.* to centreon@'172.25.128.43' identified by '123456';
mysql> grant all privileges ON centreon_status.* to centreon@'172.25.128.45' identified by '123456';
mysql> flush privileges;
mysql> select Host, User, Password from user;
六：修改centreon配置文件，配置目标数据库
[root@test mysql]# cd /usr/local/centreon/etc
centreon.conf.php
conf.pm
七： 修改ndo2db数据库访问配置文件（可能需要修改数据库内的配置文件参数）
直接在centreon修改
八：RRD数据库暂时留在源库，不做操作
[root@sznagios lib]# du -sh status
11G status
[root@sznagios lib]# du -sh metrics/
36G metrics/
[root@sznagios lib]# pwd
/usr/local/centreon/var/lib
[root@sznagios lib]#
九：参数文件配置
源 参数文件：
[root@sznagios etc]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
innodb_file_per_table = 1
# Disabling symbolic-links is recommended to 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
#
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
目标 参数文件：
[root@sznagios etc]# cat /etc/my.cnf
[mysqld]
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
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29500582/viewspace-1426170/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29500582/viewspace-1426170/
437
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
