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 狀態的變數，可以看到這個變數

的作用。

(--&gt表示要調整的值) 根據實體記憶體設定規則如下：

1G ---&gt 8

2G ---&gt 16

3G ---&gt 32

>3G ---&gt 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

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
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

The latest information about MySQL is available on the web at

Support MySQL by buying support/licenses at

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

Mysql for nagios 遷移方案

相關文章