mysql多例項部署

一、資料庫環境介紹
Mysql是oracle公司提供的開源資料庫，可以透過重新編譯來實現個性化需求。根據客要求，作業系統是Cent OS，要求mysql版本為5.0 。根據這一情況，我們決定透過rpm檔案來進行安裝，安裝版本為:
MySQL-server-5.5.15-1.rhel4.i386.rpm
MySQL-client-5.5.15-1.rhel5.i386.rpm
二、安裝過程
1．安裝資料庫軟體及自帶庫
某些linux系統自帶有mysql資料庫的安裝包，可以直接用，但為了需要我們需要安裝一些新的，所以必須先解除安裝掉再安裝，如下：
[root@ggate1 u02]# rpm -qa | grep -i mysql
mysql-5.0.77-3.el5
[root@ggate1 u02]# rpm -ev --nodeps mysql-5.0.77-3.el5
[root@ggate1 u02]# rpm -ivh MySQL-server-5.5.15-1.rhel4.i386.rpm
Preparing...            ########################################### [100%]
    1:MySQL-server     ########################################### [100%]

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h ggate1 password 'new-password'

Alternatively you can run:
/usr/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.

Please report any problems with the /usr/bin/mysqlbug script!
2．安裝客戶端軟體
安裝客戶端軟體，主要是為方面在伺服器上進行一些引數配置操作及效能調整等
[root@ggate1 u02]# rpm -ivh MySQL-client-5.5.15-1.rhel5.i386.rpm
Preparing...                ########################################### [100%]
   1:MySQL-client           ########################################### [100%]
3．配置密碼檔案
[root@ggate1 u02]# /etc/init.d/mysql start
Starting MySQL....[  OK  ]
[root@ggate1 u02]# /usr/bin/mysqladmin -u root password 'password'

--以上工作其實是白做了，後來客戶要求用作業系統自帶的mysql版本，mysql5.1，害得我們重新安裝作業系統，非常鬱悶！

以下部分就是從5.1上配置開始的

三、新增例項配置
Mysql多例項的配置。Mysql每個例項裡面包含多個資料庫，但每個資料庫相當於oracle裡的一個schema，所以從表相上看是多個資料庫，實際上是一些schema級別的。每個例項有自己獨立的配置，可以把具體的配置寫到同一個my.cnf檔案中，透過mysqld_multi來啟動每個例項，不需要的時候就進行關閉。下面進行多例項配置：
1. 建mysql使用者
[root@localhost ~]# useradd -G mysql mytest
[root@localhost ~]# passwd mytest
Changing password for user mytest.
New password: 
BAD PASSWORD: it is based on a dictionary word
Retype new password: 
passwd: all authentication tokens updated successfully.
2. 建資料庫例項目錄
[root@localhost ~]# mkdir  /home/mytest/mysql/datafile
[root@localhost ~]# mkdir -p /home/mytest/mysql/log
[root@localhost ~]# mkdir -p /home/mytest/mysql/logbin
3. 配置引數檔案
[root@localhost ~]# vi /home/mytest/mysql/my.cnf
[mysqld_multi]
mysqld          = /usr/bin/mysqld_safe
mysqladmin      = /usr/bin/mysqladmin

user            = mytest
#password        = password

[client]
#port= 3306
#socket= /var/lib/mysql/mysql.sock


[mysqld20]
port= 3320
socket= /home/mytest/mysql/mysql.sock
pid-file        = /home/mytest/mysql/mysql.pid
log-error       = /home/mytest/mysql/mysql.err
language        = /usr/share/mysql/english/
datadir         = /home/mytest/mysql
basedir         = /home/mytest/mysql
log-bin         = /home/mytest/mysql/logbin/mysql-bin
log-bin-index   = /home/mytest/mysql/logbin/mysql-bin.index
user            = mytest
innodb_log_group_home_dir = /home/mytest/mysql/log
#skip-locking


back_log = 50
join_buffer_size = 4M
max_connections = 150
table_cache = 256
thread_cache = 32
thread_concurrency = 16
tmp_table_size = 128M
default-character-set = gbk
query_cache_size = 16M
query_cache_type = 1


skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 512K
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 8M

#log-bin=mysql-bin

binlog_format=mixed

server-id= 1

innodb_data_home_dir = /home/mytest/mysql/datafile
innodb_data_file_path = ibdata1:10M:autoextend

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

模板放在/etc/sqlmodule/module.cnf
4．更改資料夾及檔案屬性
[root@localhost ~]# chown -R mytest:mysql /home/mytest/mysql
[root@localhost ~]# cd /home
[root@localhost home]# ls -ll
total 24
drwx------. 5 jrb07  jrb07   4096 Mar  9 12:50 jrb07
drwx------. 2 root   root   16384 Mar  9 10:45 lost+found
drwx------. 5 mytest mytest  4096 Mar  9 12:55 mytest
[root@localhost home]# cd mysql
-bash: cd: mysql: No such file or directory
[root@localhost home]# cd mytest
[root@localhost mytest]# ls -ll
total 4
drwxr-xr-x. 5 mytest mysql 4096 Mar  9 12:59 mysql
[root@localhost mytest]# cd mysql
[root@localhost mysql]# ls -ll
total 16
drwxr-xr-x. 2 mytest mysql 4096 Mar  9 12:55 datafile
drwxr-xr-x. 2 mytest mysql 4096 Mar  9 12:55 log
drwxr-xr-x. 2 mytest mysql 4096 Mar  9 12:55 logbin
-rw-r--r--. 1 mytest mysql 1474 Mar  9 12:59 my.cnf
5. 初始化例項，建立基本資料字典
[root@localhost mysql]# su -mytest
[mytest@localhost ~]$ mysql_install_db --datadir=/home/mytest/mysql --user=mytest
Installing MySQL system tables...
120309 13:04:36 [Warning] Ignoring user change to 'mytest' because the user was set to 'mysql' earlier on the command line

OK
Filling help tables...
120309 13:04:36 [Warning] Ignoring user change to 'mytest' because the user was set to 'mysql' earlier on the command line

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:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:
/usr/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 /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!
6. 啟動例項
[mytest@localhost ~]$ mysqld_multi --defaults-file=/home/mytest/mysql/my.cnf start 20
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Fri Mar  9 13:05:46 2012

Starting MySQL servers
[mytest@localhost ~]$ 120309 13:05:46 mysqld_safe Logging to '/home/mytest/mysql/mysql.err'.
120309 13:05:46 mysqld_safe Starting mysqld daemon with databases from /home/mytest/mysql

--注意：這裡啟動後，會停在這裡，其實例項已經啟動，可以開啟另外視窗檢查

[mytest@localhost ~]$ ps -ef | grep mysql

mytest   27232     1  0 13:05 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --port=3320 --socket=/home/mytest/mysql/mysql.sock --pid-file=/home/mytest/mysql/mysql.pid --log-error=/home/mytest/mysql/mysql.err --language=/usr/share/mysql/english/ --datadir=/home/mytest/mysql --basedir=/home/mytest/mysql --log-bin=/home/mytest/mysql/logbin/mysql-bin --log-bin-index=/home/mytest/mysql/logbin/mysql-bin.index --user=mytest --innodb_log_group_home_dir=/home/mytest/mysql/log --back_log=50 --join_buffer_size=4M --max_connections=150 --table_cache=256 --thread_cache=32 --thread_concurrency=16 --tmp_table_size=128M --default-character-set=gbk --query_cache_size=16M --query_cache_type=1 --skip-external-locking --key_buffer_size=16M --max_allowed_packet=1M --table_open_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --read_buffer_size=512K --read_rnd_buffer_size=2M --myisam_sort_buffer_size=8M --binlog_format=mixed --server-id=1 --innodb_data_home_dir=/home/mytest/mysql/datafile --innodb_data_file_path=ibdata1:10M:autoextend
mytest   27484 27232  0 13:05 pts/1    00:00:00 /usr/libexec/mysqld --basedir=/home/mytest/mysql --datadir=/home/mytest/mysql --language=/usr/share/mysql/english/ --log-bin=/home/mytest/mysql/logbin/mysql-bin --log-bin-index=/home/mytest/mysql/logbin/mysql-bin.index --innodb_log_group_home_dir=/home/mytest/mysql/log --back_log=50 --join_buffer_size=4M --max_connections=150 --table_cache=256 --thread_cache=32 --thread_concurrency=16 --tmp_table_size=128M --default-character-set=gbk --query_cache_size=16M --query_cache_type=1 --skip-external-locking --key_buffer_size=16M --max_allowed_packet=1M --table_open_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --read_buffer_size=512K --read_rnd_buffer_size=2M --myisam_sort_buffer_size=8M --binlog_format=mixed --server-id=1 --innodb_data_home_dir=/home/mytest/mysql/datafile --innodb_data_file_path=ibdata1:10M:autoextend --log-error=/home/mytest/mysql/mysql.err --pid-file=/home/mytest/mysql/mysql.pid --socket=/home/mytest/mysql/mysql.sock --port=3320
mytest   27502 27143  0 13:06 pts/1    00:00:00 grep mysql
7. 授權及更改資料庫使用者管理密碼
[mytest@localhost ~]$ mysql -uroot -p -S /home/mytest/mysql/mysql.sock
Enter password: （這裡沒有密碼，直接回車）
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.52-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> delete from user;
Query OK, 5 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges  on *.* to root@'localhost'   identified   by 'qazxs w';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges  on *.* to mytest@'localhost'   identified   by 'qazxsw w';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from user;
+--------+-----------+-------------------------------------------+
| user   | host      | password                                  |
+--------+-----------+-------------------------------------------+
| root   | localhost | *F83365F186D5B45C390EBD9B01A200B1D113D0BC |
| mytest | localhost | *437F1809645E0A92DAB553503D2FE21DB91270FD |
+--------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> update user set host='%' where user = 'mytest';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
  select user,host,password from user;
+--------+-----------+-------------------------------------------+
| user   | host      | password                                  |
+--------+-----------+-------------------------------------------+
| root   | localhost | *F83365F186D5B45C390EBD9B01A200B1D113D0BC |
| mytest | %         | *437F1809645E0A92DAB553503D2FE21DB91270FD |
+--------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

至此，一個例項 已經建立起來，並且可以完全使用！
四、例項測試
新建資料庫（在例項4上進行的）
mysql> create database mytest;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytest             |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
新建表
mysql> create table mytable (id int,name char(20),birthdate date);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| mytable          |
+------------------+
1 row in set (0.00 sec)
五、主要引數說明
MySQL 支援超過 100  個的可調節設定；但是幸運的是，掌握少數幾個就可以滿足大部分需要。查詢第二世界整理釋出這些設定的正確值可以透過 SHOW  STATUS 命令檢視狀態變數，從中可以確定 mysqld的運作情況是否符合我們的預期。給緩衝區和快取分配的記憶體不能超過系統中的現有記憶體，因此調優通常都需要進行一些妥協。

1. skip-external-locking
避免MySQL的外部鎖定，減少出錯機率增強穩定性。
2. back_log
指定MySQL可能的連線數量。當MySQL主執行緒在很短的時間內接收到非常多的連線請求，該引數生效，主執行緒花費很短的時間檢查連線並且啟動一個新執行緒。back_log引數的值指出在MySQL暫時停止響應新請求之前的短時間內多少個請求可以被存在堆疊中。如果系統在一個短時間內有很多連線，則需要增大該引數的值，該引數值指定到來的TCP/IP連線的偵聽佇列的大小。不同的作業系統在這個佇列大小上有它自己的限制。試圖設定back_log高於你的作業系統的限制將是無效的。預設值為50。對於Linux系統推薦設定為小於512的整數。
3.key_buffer_size
指定用於索引的緩衝區大小，增加它可得到更好的索引處理效能。
4. sort_buffer_size
查詢排序時所能使用的緩衝區大小。注意：該引數對應的分配記憶體是每連線獨佔！如果有100個連線，那麼實際分配的總共排序緩衝區大小為100 *6＝ 600MB
5. read_buffer_size
讀查詢操作所能使用的緩衝區大小，每連線獨享。
6. join_buffer_size 
聯合查詢操作所能使用的緩衝區大小，每連線獨享
7. max_connections 
指定MySQL允許的最大連線程式數。
8. table_cache
開啟表快取總數,可以避免頻繁的開啟資料表產生的開銷
9.thread_cache
快取可重用的執行緒數
10.thread_concurrency
最大併發執行緒數,cpu數量*2
11. log-slow-queries\long_query_time
記錄慢查詢,然後對慢查詢
12. tmp_table_size
臨時快取區，用於排序等
13. Default-character-set
字符集引數
14. query_cache_size
查詢快取區


六、使用者許可權管理
對每個各設定一個管理員使用者，用於資料庫的日常管理。設定的使用者，只能管理自己的例項，而無權管理或者訪問其它例項。
根據經驗，通常對各例項設定的使用者名稱為mysql或者root。設定好的使用者及密碼會寫入相應例項的資料字典，在進行登入或者管理時，各自使用自己資料字典裡的使用者和密碼進行驗證。
例如：在測試環境上的機器例項說明：
mysql -uroot -p -S /home/mydata/mysql1/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.1.52-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
在上面輸入的密碼是：password1，透過資料字典驗證透過！
如果不用密碼，那麼資料庫會報錯：
[root@localhost mysql]# mysql -uroot  -S /home/mydata/mysql1/mysql.sock
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
七、管理資料庫
1.客戶透過mysql連線到資料庫對資料庫進行管理，如toad、mysql客戶端、mysql workbrench等工具，對例項進行管理，包括對該例項下的資料庫、表、索引、儲存過程、臨時引數等等進行管理。
2.建立sftp使用者，透過sftp使用者來修改引數，ftp的設定由其他人配置。
3.資料檔案目錄：
/home/username1/mysql/log        ----日誌資料夾
/home/username1/mysql/test       ----mysql自帶空資料庫夾
/home/username1/mysql/mysql     ----mysql資料庫，mysql其它資料庫執行基礎
/home/username1/mysql/help      ----幫助資料夾
/home/username1/mysql/my.cnf    ----引數配置檔案
/home/username1/mysql/mysql.err  ----錯誤檔案
/home/username1/mysql/datafile   ----資料資料夾
/home/mysql1/mydata/logbin/     ----二進位制日誌檔案
/home/mysql1/mydata/logbin/mysql-bin.index
以上資料夾是資料啟動必須的，請不要刪除。對於配置檔案的修改，請首先做好備份後才予修改，否則可能導致資料庫例項不能啟動影響應用！
4.透過作業系統使用者對目錄進行配置讀寫許可權後，在使用者新增資料檔案過程中，只有在引數檔案中配置自己相應的相應目錄，在可能實現啟動例項成功！
八、資料備份
客戶備份：
客戶可以根據自己的情況對自己所負責的例項進行備份。備份的檔案放到客戶自己本地，包括資料檔案、引數檔案等。在備份的過程中，可以採用mysqldump命令或者其它第三方工具，備份策略可以根據自己的情況制定。
資訊中心備份：
採用nbu備份方式進行，主要配置需要其他同事進行說明！
在本地機上備份指令碼：
Su – mytest
Vi /home/mytest/mysql/backup.sh
mysqldump -uroot -pqazxsw11 -S /home/mytest/mysql/mysql.sock --all-databases --default-character-set=latin1 >/home/backup/mytest/full_dump$F_date.sql
rm -rf `find /home/backup/mytest/* -atime +10`

自動執行指令碼：
Crontab –e 
1 1 * * * /home/mytest/mysql/backup.sh

在這裡，都是在mytest使用者下操作的，並且希望將backup.sh放到/home/mytest/mysql/