(转载)mysql数据库升级为5.5并且设置主从同步

 

MySQL 5.5系列成为稳定版已经有一段时间了，但据我调查了解，在生产环境中还是以5.1系列为主。在国内的大公司里，只确定金山在使用5.5了。

公司的其中几台广告统计服务器，之前的运维直接用了自带安装的MySQL 5.0系列。新来的程序员写了新的广告统计程序，使用了innodb存储引擎，偶发现5.0对innodb引擎支持不怎么好，数据库并发连接数多时会出现丢连接，于是就想升级到5.5试试。因已安装了5.0，所以不想编译安装了。就直接找了一个国外编译好的5.5的yum源，直接升级，跑了一个月了，发现很稳定！就分享一下：

1、安装MySQL 5.5.x的yum源：
rpm -Uvh http://repo.webtatic.com/yum/centos/5/latest.rpm

2、安装MySQL客户端的支持包：
yum install libmysqlclient15 --enablerepo=webtatic

3、卸载MySQL老版本的软件包：
yum remove mysql mysql-*

4、安装MySQL 5.5的客户端和服务端：
yum install mysql55 mysql55-server --enablerepo=webtatic

5、启动MySQL系统服务，更新数据库：

/etc/init.d/mysqld restart
mysql_upgrade


6、附：此台服务器的my.cnf配置

skip-locking
skip-name-resolve
key_buffer = 1024M
back_log = 3000
max_allowed_packet = 4M
table_cache = 512
sort_buffer_size = 8M
read_buffer_size = 8M
myisam_sort_buffer_size = 1024M
thread_cache = 512
query_cache_size = 512M
set-variable = wait_timeout=60
thread_concurrency = 4
log-slow-queries = slow.log
long_query_time = 1
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 1024M
#innodb_locks_unsafe_for_binlog = 1


如果不是升级，而是新安装。还需要设置root密码，删除默认的空用户、空密码等等，这里就不说了。请参考本博其他博文或官方手册，或者google。

关于my.cnf配置更详细的说明请参考：

http://www.ha97.com/4110.html

英文原文：http://www.webtatic.com/packages/mysql55/

转自 
http://blog.sina.com.cn/s/blog_4b75b0020100lpwe.html 

replicate-ignore-db? 

主服务器:CentOS 5.4 Mysql 5.5.3 -m3 源码 IP:192.168.1.22 
从服务器:CentOS 5.4 Mysql 5.5.3 -m3 源码 IP:192.168.1.33 

配置: 
一、主服务器 
1.1、创建一个复制用户，具有replication slave 权限。 
mysql>grant replication slave on *.* to ‘slave001’@’192.168.1.22′ identified by ‘slave001’; 
1.2、编辑my.cnf文件 
vi my.cnf 
添加 
server-id=1 
并开启log-bin二进制日志文件 
log-bin=mysql-bin 
1.3、启动mysql数据库 
service mysqld start 
1.4、设置读锁 
mysql>flush tables with read lock; 
1.5、得到binlog日志文件名和偏移量 
mysql>show master status; 
+——————+———-+————–+——————+  
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
+——————+———-+————–+——————+  
| mysql-bin.0000010 | 106| | |  
+——————+———-+————–+——————+ 
1.6、备份要同步的数据库 
mysqldump test1 > test1.sql 
或者 
/usr/local/mysql/3306/bin/mysqldump -S/usr/local/mysql/3306/mysqld3306.sock -uusername -ppsw --add-drop-table --add-locks --default-character-set=utf8 --hex-blob -R --opt --all-databases > allDBdump.sql 
===================================================================== 
常见选项： 
--add-drop-table：添加DROP TABLE语句 
--add-locks：用LOCK TABLES 和 UNLOCK TABLES  语句封装插入每个表格的语句，能加速数据导入速度 
--all-databases, -A： 备份所有数据库 
--compress, -C：用于在两个server之间复制时压缩数据 
--databases, -B： 用于备份多个数据库，如果没有该选项，mysqldump 把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldum把每个名字都当作为数据库名。CREATE DATABASE IF NOT EXISTS db_name and USE db_name 会被添加到导出文件里。 
--force, -f：即使发现sql错误，仍然继续备份 
--host=host_name, -h host_name：备份主机名，默认为localhost 
--no-data, -d：只导出表结构 
--opt：等同于激活--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset这些选项 
--password[=password], -p[password]：密码 
--port=port_num, -P port_num：制定TCP/IP连接时的端口号 
--protocol={TCP | SOCKET | PIPE | MEMORY}：制定连接所用的协议 
--quick, -q：快速导出 
--socket=path, -S path：当连接至localhost时，制定socket文件 
--tables：覆盖 --databases or -B选项，后面所跟参数被视作表名 
--user=user_name, -u user_name：用户名 
--xml, -X：导出为xml文件 
===================================================================== 
1.7、解锁 
mysql>unlock tables; 
二、从服务器 
2.1、编辑my.cnf文件 
vi my.cnf 
添加 
server-id=2 
注:需要把默认的server-id=1去掉 
不要尝试把master配置属性写在my.cnf 中，5.1.7以后，mysql已经不支持这样做了 
2.2、启动从数据库 
service mysqld start 

2.3、对从数据库进行相应设置 
mysql> change master to  
-> master_host='192.168.1.22', 
-> master_port=3306, 
-> master_user='slave001', 
-> master_password='slave001', 
-> master_log_file='mysql-bin.0000010', 
-> master_log_pos=106, 
-> replicate_wild_ignore_table=performance_schema.%, 
-> replicate_wild_ignore_table=mysql.%, 
-> replicate_wild_ignore_table=information_schema.%, 
-> replicate_wild_ignore_table=test.% 
2.4、启动从服务器slave线程 
mysql>start slave; 
执行show processlist命令显示以下进程: 
mysql>show processlist\G 

*************************** 2. row ***************************  
Id: 2  
User: system user  
Host:  
db: NULL  
Command: Connect  
Time: 2579  
State: Has read all relay log; waiting for the slave I/O thread to update it 
Info: NULL表示slave已经连接上master，开始接受并执行日志 
2.5、查看slave线程状态 
mysql>show slave status;  
*************************** 1. row ***************************  
Slave_IO_State: Waiting for master to send event  
Master_Host: 192.168.1.22  
Master_User: repl  
Master_Port: 3306  
Connect_Retry: 60  
Master_Log_File: mysql-bin.0000010  
Read_Master_Log_Pos: 106  
Relay_Log_File: centos-relay-bin.000002  
Relay_Log_Pos: 529  
Relay_Master_Log_File: mysql-bin.0000010  
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: 106  
Relay_Log_Space: 830  
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:  
1 row in set (0.00 sec) 
验证是否配置正确 
在从服务器上执行 
mysql> show slave status\G 
Waiting for master to send event  
Slave_IO_Running: Yes  
Slave_SQL_Running: Yes 
如以上二行同时为Yes 说明配置成功 
PS：show slave status\G 后不要添加 ； 号， 不然会出 ERROR 
测试： 
1、在主服务器test数据库中创建user表 
mysql>use test1;  
mysql>create table user(id int); 
2、在从服务器中查看user表 
mysql>use test1;  
mysql> show tables like ‘user’;  
+———————-+  
| Tables_in_test(user) |  
+———————-+  
| user |  
+———————-+  
1 row in set (0.00 sec) 
说明主从数据同步成功。 
常见问题归纳： 
1.在从数据库中查看slave状态时出现 
The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it) 
说明方从服务器里my.cnf中的server-id有相同的。 
解决办法: 
修改my.cnf里的server-id，并重启数据库服务。my.cnf文件默认有server-id=1 
其它说明 
主服务器my.cnf 
#binlog-do-db=需要备份的数据库名，可写多行 
#binlog-ignore-db=不需要备份的数据库名，可写多行 
从服务器my.cnf 
配置已经无用，要在mysql中执行 

三、手动更新 
在主服务器上执行 
mysql>flush tables with read lock;  
Query OK,rows affected (0.01 sec)  
mysql>show master status;  
+——————+———-+————–+——————+  
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
+——————+———-+————–+——————+  
| mysql-bin.0000011 | 260| | |  
+——————+———-+————–+—————— 
在从服务器上执行 
mysql>select master_pos_wait(‘mysql-bin.0000011′,’260′);  
+————————————————–+  
| master_pos_wait(‘mysql-bin.0000011′,’260′) |  
+————————————————–+  
| 0 |  
+————————————————–+  
1 row in set (0.01 sec) 
注意，执行这条语句的时候，可能会有MySQL server has gone away 错误，木有关系，多执行几次就好了 

同步完成后，在主服务器上执行解锁 
mysql>unlock tables; 

一旦同步出错，有以下解决方法： 
一是在my.cnf里加 
slave-skip-errors=1062 
1062为错误号 

二是在备机跳过执行一条语句:set global sql_slave_skip_counter=1 
1可以为N.