分类： Mysql/postgreSQL
2011-07-16 14:22:05
mysql master-multi_slaves+lvs拓扑图。
一、编译安装MySQL mysql-5.5.11
(1) 添加用户，创建目录
/usr/sbin/useradd mysql
mkdir -p /usr/local/mysql/
mkdir -p /data/mysql
mkdir -p /usr/local/mysql/binlog/
mkdir -p /usr/local/mysql/relaylog/ （如果mysql_slave服务器就执行这个命令，master不执行）
(2) 下载并安装mysql
mkdir –p /root/soft ;cd soft
wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.11.tar.gz/from/http://mysql.he.net/
wget
wget
(3) 安装 cmake
cd /root/soft
tar zxvf cmake-2.8.4.tar.gz
cd cmake-2.8.4
./bootstrap
gmake
gmake install
cd ../
tar zxvf bison-2.5.tar.gz
cd bison-2.5
./configure
make
make install
cd ../
tar xvf mysql-5.5.11.tar.gz
cd mysql-5.5.11/
cmake 参数参考http://dev.mysql.com/doc/refman/5.5/en/source-configuration-options.html
make
make install
ln -s /usr/local/mysql/lib/libmysqlclient.so.16 /usr/lib/libmysqlclient.so.16
二、优化mysql启动参数并启动mysql服务
(1)优化配置mysql配置文件
vim /usr/local/mysql/my.cnf 加入下面的内容。
[client]
character-set-server = utf8
port = 3306
socket = /tmp/mysql.sock
[mysqld]
server-id = 1
user = mysql
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql/
log-error = /usr/local/mysql/mysql_error.log
pid-file = /usr/local/mysql/mysql.pid
log-bin = /usr/local/mysql/binlog/binlog
binlog_cache_size = 10M
binlog_format = MIXED
max_binlog_cache_size = 1G
max_binlog_size = 1G
expire_logs_days = 30
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/slow.log
long_query_time = 10
log-queries-not-using-indexes
character-set-server = utf8
collation-server = utf8_general_ci
skip-character-set-client-handshake
open_files_limit = 10240
back_log = 600
max_connections = 5000
max_connect_errors = 6000
table_cache = 256
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 2M
join_buffer_size = 1M
thread_cache_size = 16
#thread_concurrency = 8
query_cache_size = 512M
query_cache_limit = 2M
query_cache_min_res_unit = 2k
default-storage-engine = MyISAM
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 500M
max_heap_table_size = 246M
key_buffer_size = 256M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
replicate-ignore-db = information_schema,mysql,performance_schema,test
#binlog_do_db =
interactive_timeout = 2880000
wait_timeout = 2880000
skip-name-resolve
#master-connect-retry = 10
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1G
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 20
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 32M
(2)更改相关文件的权限
chmod +w -R /usr/local/mysql/
chown -R mysql:mysql /usr/local/mysql/
chmod +w -R /data/mysql
chown -R mysql:mysql /data/mysql
(3)以mysql用户身份初始化数据库：
ulimit -SHn 51200(4)启动MySQL（最后的&表示在后台运行）
/bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/my.cnf &
三、初始化root用户密码，并对slave服务器授权。
(5)为root用户设置密码
/usr/local/mysql/bin/mysqladmin -u root password '123456'
(6)对slave服务器授权
/usr/local/mysql/bin/mysql -u root -p123456
mysql>CREATE USER 'slave1'@'192.168.222.195' IDENTIFIED BY 'slave1';
mysql>GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'192.168.222.195';
mysql>CREATE USER 'slave2'@'192.168.222.205' IDENTIFIED BY 'slave2';
mysql>GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'192.168.222.205';
mysql>CREATE USER 'slave3'@'192.168.222.223' IDENTIFIED BY 'slave3';
mysql>GRANT REPLICATION SLAVE ON *.* TO 'slave3'@'192.168.222.223';
mysql>flush privileges;
到此对master主机的配置已经结束
四、对slave服务器安装配置。
对slave1,slave2,slave3的安装配置除my.cnf有个别地方不相同外，安装，初始化，启动，mysql数据库和master相同
slave1的配置文件my.cnf中server-id = 2，slave2的配置文件my.cnf中server-id = 3，slave3的配置件my.cnf中server-id = 4
以slave1的配置文件my.cnf为例如下
[client]
character-set-server = utf8
port = 3306
socket = /tmp/mysql.sock
[mysqld]
server-id = 2
#master-host = 192.168.222.174
#master-user = salve1
#master-password = slave1
#master-port = 3306
log-slave-updates
relay-log-index = /usr/local/mysql/relaylog/relaylog
relay-log-info-file = /usr/local/mysql/relaylog/relaylog
relay-log = /usr/local/mysql/relaylog/relaylog
user = mysql
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql/
log-error = /usr/local/mysql/mysql_error.log
pid-file = /usr/local/mysql/mysql.pid
#log-bin = /usr/local/mysql/binlog/binlog
#binlog_cache_size = 10M
#binlog_format = MIXED
#max_binlog_cache_size = 1G
#max_binlog_size = 1G
expire_logs_days = 30
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/slow.log
long_query_time = 10
log-queries-not-using-indexes
character-set-server = utf8
collation-server = utf8_general_ci
skip-character-set-client-handshake
open_files_limit = 10240
back_log = 600
max_connections = 5000
max_connect_errors = 6000
table_cache = 256
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 2M
join_buffer_size = 1M
thread_cache_size = 16
#thread_concurrency = 8
query_cache_size = 512M
query_cache_limit = 2M
query_cache_min_res_unit = 2k
default-storage-engine = MyISAM
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 500M
max_heap_table_size = 246M
key_buffer_size = 256M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
replicate-ignore-db = information_schema,mysql,performance_schema,test
#binlog_do_db =
interactive_timeout = 2880000
wait_timeout = 2880000
#skip-name-resolve
#master-connect-retry = 10
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1G
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 20
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 32M
五、对slave机器做slave服务相关设置(以slave1为例)
参考master数据库的初始化，启动，root密码设置为例对slave机器做相同操作
(1)在master机器上运行show master status;假设结果如下
mysql> show master status;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000003 | 2557 | | |
+---------------+----------+--------------+------------------+
1 row in set (0.01 sec)
(2) 在slave1上进行的设置。
1 、 停止 slave 的服务
mysql> slave stop;
2 、 设置主服务器的各种参数
mysql> CHANGE MASTER TO
-> MASTER_HOST=' 192.168.222.174 ', // 主服务器的 IP 地址
-> MASTER_USER=' slave1 ', // 同步数据库的用户
-> MASTER_PASSWORD='slave1 ', // 同步数据库的密码
-> MASTER_LOG_FILE=' binlog.000003 ', // 主服务器二进制日志的文件名
-> MASTER_LOG_POS= 2557 ; // 日志文件的开始位置
3 、 启动同步数据库的线程
mysql> slave start;
4、在slave1上运行show slave status结果如下。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.222.174
Master_User: slave1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 2557
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 1313
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2557
Relay_Log_Space: 1462
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
看到Slave_IO_Running和Slave_SQL_Running的参数都为 Yes，就说明主从同步运行正常
对参考slave1对salve2，slave3相应配置
六、用lvs对slave1，slave2,slave3 做负载均衡（采用DR模式）
1、在slave1,slave2,slave3上的相关配置
(1)关闭ARP转发。（使公网的DGW发arp广播时，只有director 的VIP回应，因为realserver的lo也配置了VIP地址 ）
echo 1 > /proc/sys/net/ipv4/conf/eth0/arp_ignore
echo 2 > /proc/sys/net/ipv4/conf/eth0/arp_announce
(2) ifconfig lo:1 192.168.222.196 netmask 255.255.255.255
2、在slave1上的配置
(1)在slave1上安装ipvsadm
yum install ipvsadm
(2)添加虚拟网卡
ifconfig eth0:1 192.168.222.196 netmask 255.255.255.0
(3)lvs分发策略设置。
ipvsadm -A -t 192.168.222.196:3306 -s rr
ipvsadm -a -t 192.168.222.196:3306 -r 192.168.222.195 -g
ipvsadm -a -t 192.168.222.196:3306 -r 192.168.222.205 -g
ipvsadm -a -t 192.168.222.196:3306 -r 192.168.222.223 -g
七、建立测试用户
(1)在master服务器上建立测试用户，用于写操作。
mysql> create user 'master'@'%' identified by 'master';
mysql> grant all on *.* to 'master'@'%';
mysql> flush privileges;
(2)在salve上进行如下相同操作建立用户lvs用于查询操作。
mysql> create user 'lvs'@'%' identified by 'lvs';
mysql> grant select on *.* to 'lvs'@'%';
mysql> flush privileges;
