mysql 5.5.24安装步骤
下载:
wget ftp://ftp.easynet.be/mysql/Downloads/MySQL-5.5/mysql-5.5.24.tar.gz
yum install gcc gcc-c++ libtool autoconf -y
yum install automake imake libxml2-devel expat-devel ncurses-devel cmake bison -y
tar zxvf mysql-5.5.24.tar.gz
cd mysql-5.5.24
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/home/mysql/ \
-DWITH_MYISAM_STORAGE_ENGINE=1\
-DWITH_INNOBASE_STORAGE_ENGINE=1\
-DWITH_ARCHIVE_STORAGE_ENGINE=1\
-DWITH_BLACKHOLE_STORAGE_ENGINE=1\
-DENABLED_LOCAL_INFILE=1\
-DDEFAULT_CHARSET=utf8\
-DDEFAULT_COLLATION=utf8_general_ci\
-DEXTRA_CHARSETS=all\
-DWITH_SSL=system\
-DMYSQL_TCP_PORT=3306
make
make install
cd /usr/local/mysql
chown -R mysql .
chgrp -R mysql .
cp support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
/usr/local/mysql/scripts/mysql_install_db--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql &
vim /etc/my.cnf
[client]
port=3306
socket= /tmp/mysql.sock
# The MySQL server
[mysqld]
port=3306
socket= /tmp/mysql.sock
datadir= /data/mysql
skip-name-resolve
skip-external-locking
table_open_cache=70
read_buffer_size=256K
read_rnd_buffer_size=512K
thread_concurrency=16
########################### INNODB ############################
innodb_file_per_table=1
innodb_buffer_pool_size=20M
innodb_flush_log_at_trx_commit=0
#init_connect='SET autocommit=0'
innodb_log_file_size=40M
innodb_additional_mem_pool_size=16M
innodb_log_buffer_size=6M
innodb_doublewrite=0
innodb_max_dirty_pages_pct=80
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=0
####################### Connections ##########################
#back_log=500
max_connections=1024
max_connect_errors=99999
max_allowed_packet=2M
table_cache=4096
thread_cache=64
open_files_limit=10000
net_buffer_length=8K
####################### Buffer Size ###########################
key_buffer=3072M
max_heap_table_size=32M
myisam_sort_buffer_size=8M
#query_cache_size=128M
sort_buffer_size=512K
tmp_table_size=64M
###############################################################
interactive_timeout=30
wait_timeout=30
connect_timeout=10
#slow-query-log
#long_query_time=2
slow-query-log
slow_launch_time=2
slow-query-log-file=/data/mysql/db001-slow.log
######################## Master-Slave ########################
server-id=1
log-bin=mysql-bin
binlog_format=mixed
log_slave_updates=1
expire_logs_days=30
lower_case_table_names=1
slave-skip-errors=all
##############################################################
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer=8M
sort_buffer_size=8M
[myisamchk]
key_buffer=8M
sort_buffer_size=8M
[mysqlhotcopy]
interactive-timeout
##增加系统服务
/sbin/chkconfig --add mysqld
/sbin/chkconfig mysqld on
/sbin/service mysqld start
netstat -tnlp |grep 3306
exportPATH=$PATH:/usr/local/mysql/bin/
mysql 5.5.24
主从配置
master 192.168.0.211
slave 192.168.0.210
在master的配置文件中加上
server-id=1
binlog-do-db=test01
binlog-ignore-db=mysql
在slave的配置文件上加上
server-id=2
relay-log=server02-relay-bin
replicate-do-db=test01
replicate-ignore-db=mysql
relay-log-index=slave-relay-bin.index
###master的信息不用再加到slave的配置文件中 否则重启mysql引起找不到pid文件 master的信息只在从库中执行change语句里面执行
分别重启数据库
在master上看一下以下信息
mysql>show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 | 199 | test01 | mysql |
+------------------+----------+--------------+------------------+
并执行一下语句:
mysql>create database test01;
mysql>GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* TO 'slave'@'192.168.0.210' IDENTIFIED BY '123456';
mysql>Grant ALL PRIVILEGES ON test01.* TO slave@'%' IDENTIFIED BY '123456';
在slave上执行一下语句
mysql>CHANGE MASTER TOMASTER_HOST='192.168.0.211',MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='123456',master_log_file='mysql-bin.000008',master_log_pos=199;
mysql>slave start;
mysql>show slave status\G
能看到Slave_IO_Running: Yes
Slave_SQL_Running: Yes说明同步成功
mysql5.1与5.5主从配置的差异
1.master信息不再在从库的配置文件中指定,而是直接在从库的sql命令中使用change master语句指定
2.Server-id的规则:
Server ID. On the master and each slave, you must use the server-id option to establish a unique replication ID in the range from 1 to 232 – 1. “Unique” means that each ID must be different from every other ID in use by any other replication master or slave.
主库ID为1,从库id的取值范围为2-232 – 1 但每个slave的id必须唯一
此外官方推荐的配置配置文件中指出:
# required unique id between 2 and 2^32 – 1 #从库ID取值范围为2-232 – 1
# (and different from the master) #必须和主库区别开
# defaults to 2 if master-host is set #默认设置为2 如果只有一个从库
# but will not function as a slave if omitted #如果对id不做设置,从库将不起作用
3。在5.5从机中,skip-slave-errors=all是继续有效,但是官方文档给出的解释如下:
•--slave-skip-errors=[err_code1,err_code2,...|all]
Normally, replication stops when an error occurs on the slave. This gives you the opportunity to resolve the inconsistency in the
data manually. This option tells the slave SQL thread to continue replication when a statement returns any of the errors listed in
the option value.
正常情况下,当从库复制出现出现错误时,复制将暂停,这样就有了解决数据不一致的机会。此选项的作用是,当此选项值等于mysql返回的错误码[err_code* 、ALL]时复制继续进行
Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and
client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option
results in slaves becoming hopelessly out of synchrony with the master, with you having no idea why this has occurred.
不建议使用此选项,除非你可以断定什么原因导致了这些错误,如果在复制设置、客户端程序、以及mysql本身无bugs,能使复制进程停止的错误是不可能发生的,滥用此选项将会导致无法理想的与主库同步,而且你会对此不同步的原因将毫不知情
4.MySQL 5.5的二进制日志记录格式为MAXED
mysql>select @@session.binlog_format;
+-------------------------+
| @@session.binlog_format |
+-------------------------+
| MIXED |
+-------------------------+
1 row in set (0.00 sec)