mysqldump -d -uroot -p superadmin>d:/superadmin.sql
让mysql给某一个机器访问
flush privileges;
备份,容灾处理在平时的开发中经常会遇到,要根据的场景处理具体的业务。
在备份的过程中,我遇到过多个MASTER对应一个SLAVE,通俗一点就是多个机器对应一个机器。
大家可能都知道master,slave备份都是一个MYSQL对应另外一个MYSQL实例
但是又没有想过一个备机上有多个SLAVE呢 这个时候你就需要了解一下,mysqld_multi,mysqladmin,mysqld_safe
多个SLAVE的时候 MYSQL启动,进入CLIENT都是需要SOCKET,PORT,然后进入对应了的CLIENT。可以通过mysqladmin,mysqld_safe去启动,关闭数据库
mysql提供了多个实例的管理方案就是 mysqld_multi
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
password =test
# The MySQL server
[mysqld1]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
wait_timeout=100
interactive_timeout=100
max_connections=1000
log-bin=mysql-bin
user=root
server-id = 92001
# binary logging format - mixed recommended
binlog_format=mixed
datadir=/var/mysql/data
[mysqld2]
port = 3308
socket = /tmp/mysql3308.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
wait_timeout=100
interactive_timeout=100
max_connections=1000
log-bin=mysql-bin
server-id = 98302
user=root
# binary logging format - mixed recommended
binlog_format=mixed
datadir=/var/mysql3308/data
master-host=114.80.81.85
master-user=root
master_password=slave
master-connect-retry=10
这里的datadir,socket,port,在编译的时候必须指向不同的实例。
下面的是如果启动数据库
/usr/local/mysql/bin/mysqld_multi --defaults-file=/etc/my.cnf stop 1
/usr/local/mysql/bin/mysqld_multi --defaults-file=/etc/my.cnf start 1
如何进入数据库的CLIENT
mysql -uroot --socket=/tmp/mysql3308.sock --port=3308 -p
slave mysql备份
master 配置
binlog-do-db = db1
binlog-do-db = db2
binlog-ignore-db=mysql
replicate-do-db=db1
replicate-do-db=db2
replicate-ignore-db=mysql
如果master出问题直接转到slave配置
log-slave-updates
slave-skip-errors = all
sync_binlog = 1
server_id =2主要跟slave的server_id不一样
注意my.cnf的serverid不能一样
然后重启数据库,主要把访问权限付给slave
grant all on *.* to root@"192.168.1.1" Identified by "19870896"
flush privileges;
slave配置
slave端有可能,只复制部分表,有一些表不需要备份配置如下:
有一些表你可能做水平或则垂直的处理。如果表的前几位一样,就可以用通配符%匹配
replicate-wild-ignore-table=db1.listinglog20%
replicate-wild-ignore-table=db1.listingplan20%
replicate-wild-ignore-table=db1.showcaselog20%
当然也会遇到没有通配符的情况。解决方法如下:
replicate-ignore-table=db1.batch_item_tmp
replicate-ignore-table=db1.batchitem
配置完毕以后重启一下MYSQL
master_port=3306,
master_log_file='mysql-bin.000214',
master_log_pos=342800;
然后运行start slave;
启动完毕以后show slave status\G;
看到最后的seconds_behind_master null表示没有在通信,
这个时候停止slave.
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave
注意show slave status\G; 这个查看slave的状态
show master status\G; 这个是查看master的状态
具体查考
http://blog.zhanxb.com/post/361/
http://renxiangzyq.iteye.com/blog/684038
http://www.cnblogs.com/birdshover/archive/2010/01/27/1657670.html