CentOS 5.5 安裝 mysql-5.1.53-linux-x86_64 及主從復制集群配置

設置Mysql的主從復制的主要用途：
1 做備份機器，一旦主服務器崩潰，可以直接啟用從服務器作為主服務器
2 可以直接鎖定從服務器的表只讀，然后做備份數據，這樣不會影響主服務器的服務
3 可以處理讀寫數據庫的負載均衡

要點：
從服務器一般需要設置只讀，否則容易產生錯誤導致同步失敗
wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.53-linux-x86_64-glibc23.tar.gz/from/http://mysql.mirror.tw/
-
 

tar -C /usr/local -xzf mysql-5.1.53-linux-i686-glibc23.tar.gz
cd /usr/local
ln -s  mysql-5.1.53-linux-i686-glibc23 mysql

tar -C /usr/local -xzf  mysql-5.1.53-linux-x86_64-glibc23.tar.gz
cd /usr/local
ln -s mysql-5.1.53-linux-x86_64-glibc23 mysql

cd /usr/local/mysql
cp support-files/my-huge.cnf my.cnf

mv /etc/my.cnf ~/
mv /etc/rc.d/init.d/mysqld ~/

修改server-id = 1為服務器ip地址最后幾位，或者其它數值：
vim  my.cnf

server-id = 8108

max_allowed_packet = 64M
max_connections=800
character-set-server=utf8
expire_logs_days = 60
binlog_format=mixed

innodb_log_files_in_group=2
default_table_type = INNODB
innodb_data_home_dir = /opt/data/mysql/
innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:20M:autoextend
innodb_log_group_home_dir = /opt/data/mysql/

# 4G RAM
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit=0
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT

# perform
tmp_table_size = 512M
max_heap_table_size=128M

slow_query_log
#slow_query_log_file=mysql-slow.log
long_query_time=1
log-error=mysqld.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#————————————–
# 32G RAM
max_allowed_packet = 64M
innodb_buffer_pool_size = 24G
#————————————-

/usr/sbin/groupadd -g 502 mysql
/usr/sbin/useradd -u 502 -g mysql mysql

mkdir -p /opt/data/mysql/
chown mysql.mysql /opt/data/mysql/

cd /usr/local/mysql/
scripts/mysql_install_db –user=mysql
chown -R root .
chown -R mysql data
chgrp -R mysql .
#chmod -R u+rw data
cd /opt/data/mysql/
chown mysql.mysql /opt/data/mysql/

cd /usr/local/mysql/
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod +x /etc/rc.d/init.d/mysqld

#/sbin/chkconfig –del mysqld
/sbin/chkconfig –add mysqld
/sbin/chkconfig –level 345 mysqld on
/sbin/chkconfig –list mysqld

echo "/usr/local/mysql/lib" >> /etc/ld.so.conf
cat /etc/ld.so.conf
ldconfig

/etc/rc.d/init.d/mysqld stop
/etc/rc.d/init.d/mysqld start

mkdir -p /var/lib/mysql/
ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

#/usr/local/mysql/bin/mysqld_safe &
mkdir -p /var/run/mysqld/
chown mysql /var/run/mysqld/

#加入mysql到路徑
echo pathmunge /usr/local/mysql/bin after > /etc/profile.d/mysql.sh

#執行一下，保證mysql在路徑環境變量中
. /etc/profile

或者
vim /etc/profile
PATH=$PATH:/usr/local/mysql/bin
PATH=/usr/local/mysql/bin:$PATH

tail -50 /opt/data/mysql/*.err

slave上的配置

修改server-id = 1為服務器ip地址最后幾位，或者其它數值，必須保證唯一性：
vim  my.cnf

server-id = 8168

max_connections=600
character-set-server=utf8
binlog_format = "ROW"
default_table_type = innodb
relay-log-purge=1
#slave-skip-errors=1062,1053
skip-slave-start
replicate-ignore-db=mysql
innodb_data_home_dir = /opt/data/mysql/
innodb_data_file_path = ibdata1:2000M;ibdata2:2000M:autoextend
innodb_log_group_home_dir = /opt/data/mysql/

在master服務器上配置復制賬號權限：
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'slavepass';
flush privileges;

在slave服務器上配置master服務器信息：
CHANGE MASTER TO MASTER_HOST='10.8.8.108', MASTER_USER='replication', MASTER_PASSWORD='slavepass';
start slave;

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]