Linux CentOS 5.5 下 MySQL 5.5.3-m3 同步与主从备份

**************************前言**************************一.主从的作用：1.可以当做一种备份方式2.用来实现读写分离，缓解一个数据库的压力二.环境：OS CentOS5.5DB MySQL5.5.3-m3安装 CentOS5.5 请看 http://www.linuxidc.com/Linux/2011-05/36087.htm安装 MySQL5.5.3-m3 请看 http://www.linuxidc.com/Linux/2011-04/34964.htm三.MySQL主从备份原理master 上提供binlog ,slave 通过 I/O线程从 master拿取 binlog,并复制到slave的中继日志中slave 通过 SQL线程从 slave的中继日志中读取binlog ,然后解析到slave中四.主从复制大前提需要master与slave同步，因为笔者的数据库数据量不大，所以无需考虑太多，直接把master上的data复制到了slave上，但是如果是大的数据量，比如像taobao这个的系统那么数据同步也是很难得，需要有一个完善的方案，有兴趣的可以看看这篇文章http://www.taobaodba.com/html/564_%E5%A2%9E%E9%87%8F%E6%97%A5%E5%BF%97%E8%BF%AD%E4%BB%A3%E5%90%8C%E6%AD%A5%E5%92%8C%E9%98%BF%E5%9F%BA%E9%87%8C%E6%96%AF%E6%82%96%E8%AE%BA.html**************************开始***************************************************一.将master设置为只读。mysql> flush tables with read lock;二.用master中的data文件夹替换slave中的data文件夹比如用 tar zcvf mysql_data.gz /media/raid10/mysql/3306/data然后 mv mysql_data.gz /media/raid10/htdocs/blog/wordpress/因为我的 /media/raid10/htdocs/blog/wordpress/ 是 Nginx 的主目录所以可以在 slave上，用wget下载这个文件，然后解压，并覆盖slave上的data文件注意：覆盖之前最好备份源文件三.配置master的my.cnf,添加以下内容在[mysqld]配置段添加如下字段server-id=1log-bin=/media/raid10/mysql/3306/binlog/binlog //这里写你的binlog绝对路径名binlog-do-db=blog //需要同步的数据库，如果没有本行，即表示同步所有的数据库binlog-ignore-db=mysql //被忽略的数据库这里给出我的my.cnf配置文件[client]character-set-server = utf8port = 3306socket = /tmp/mysql.sock[mysqld]character-set-server = utf8replicate-ignore-db = mysqlreplicate-ignore-db = testreplicate-ignore-db = information_schemauser = mysqlport = 3306socket = /tmp/mysql.sockbasedir = /usr/local/webserver/mysqldatadir = /media/raid10/mysql/3306/datalog-error = /media/raid10/mysql/3306/mysql_error.logpid-file = /media/raid10/mysql/3306/mysql.pidopen_files_limit = 10240back_log = 600max_connections = 5000max_connect_errors = 6000table_cache = 614external-locking = FALSEmax_allowed_packet = 16Msort_buffer_size = 1Mjoin_buffer_size = 1Mthread_cache_size = 300#thread_concurrency = 8query_cache_size = 20Mquery_cache_limit = 2Mquery_cache_min_res_unit = 2kdefault-storage-engine = MyISAMthread_stack = 192Ktransaction_isolation = READ-COMMITTEDtmp_table_size = 20Mmax_heap_table_size = 20Mlong_query_time = 3log-slave-updateslog-bin = /media/raid10/mysql/3306/binlog/binlogbinlog-do-db=blogbinlog-ignore-db=mysqlbinlog_cache_size = 4Mbinlog_format = MIXEDmax_binlog_cache_size = 8Mmax_binlog_size = 20Mrelay-log-index = /media/raid10/mysql/3306/relaylog/relaylogrelay-log-info-file = /media/raid10/mysql/3306/relaylog/relaylogrelay-log = /media/raid10/mysql/3306/relaylog/relaylogexpire_logs_days = 30key_buffer_size = 10Mread_buffer_size = 1Mread_rnd_buffer_size = 6Mbulk_insert_buffer_size = 4Mmyisam_sort_buffer_size = 8Mmyisam_max_sort_file_size = 20Mmyisam_repair_threads = 1myisam_recoverinteractive_timeout = 120wait_timeout = 120skip-name-resolve#master-connect-retry = 10slave-skip-errors = 1032,1062,126,1114,1146,1048,1396#master-host = 192.168.1.2#master-user = username#master-password = password#master-port = 3306server-id = 1innodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 20Minnodb_data_file_path = ibdata1:56M:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 16Minnodb_log_file_size = 20Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0#log-slow-queries = /media/raid10/mysql/3306/slow.log#long_query_time = 10[mysqldump]quickmax_allowed_packet = 32M四.在master机上为slave机添加一同步帐号mysql> grant replication slave on *.* to "admin"@"172.29.141.115" identified by "12345678";mysql> flush privileges ;

Ubuntu 安装 Oracle SQL Developer 3.0CentOS 5.5安装Oracle 11g第二版相关资讯 MySQL教程

30分钟带你快速入门MySQL教程（02月03日）
MySQL教程：关于I/O内存方面的一些（01月24日）
CentOS上开启MySQL远程访问权限（01/29/2013 10:58:40）

MySQL教程：关于checkpoint机制（01月24日）
MySQL::Sandbox （04/14/2013 08:03:38）
生产环境MySQL 5.5.x单机多实例配（11/02/2012 21:02:36）

本文评论查看全部评论（0）