Mysql主从搭建及Mysql运维相关命令-优快云博客

1，安装mysql，方式很多，rpm方式安装

yum -y install mysql-server mysql-devel mysql mysql-bench mysql-test

2 ,启动

/etc/init.d/mysqld start

3,master

编辑/etc/my.cnf
#确保有如下行
server-id=1
log-bin=mysql-bin
binlog-do-db=mysql#需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可
binlog-ignore-db=mysql#不需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可
log-slave-updates#这个参数一定要加上，否则不会给更新的记录些到二进制文件里
slave-skip-errors#是跳过错误，继续执行复制操作

linux: useradd repl_user

passwd repl_user

mysql> grant<wbr style="PADDING-BOTTOM: 0px; LINE-HEIGHT: 28px; BACKGROUND-COLOR: rgb(247,252,255); MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT-FAMILY: 'Times new roman'; COLOR: rgb(51,51,51); FONT-SIZE: 16px; PADDING-TOP: 0px">replication<wbr style="PADDING-BOTTOM: 0px; LINE-HEIGHT: 28px; BACKGROUND-COLOR: rgb(247,252,255); MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT-FAMILY: 'Times new roman'; COLOR: rgb(51,51,51); FONT-SIZE: 16px; PADDING-TOP: 0px">slave<wbr style="PADDING-BOTTOM: 0px; LINE-HEIGHT: 28px; BACKGROUND-COLOR: rgb(247,252,255); MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT-FAMILY: 'Times new roman'; COLOR: rgb(51,51,51); FONT-SIZE: 16px; PADDING-TOP: 0px">on <wbr style="PADDING-BOTTOM: 0px; LINE-HEIGHT: 28px; BACKGROUND-COLOR: rgb(247,252,255); MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT-FAMILY: 'Times new roman'; COLOR: rgb(51,51,51); FONT-SIZE: 16px; PADDING-TOP: 0px">*.* <a href="mailto:'repl_user'@'slave_ip'">'</a><a href="mailto:repl_user@slave_ip">repl_user'</a><a href="mailto:repl_user@slave_ip"><wbr style="PADDING-BOTTOM: 0px; LINE-HEIGHT: 28px; BACKGROUND-COLOR: rgb(247,252,255); MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT-FAMILY: 'Times new roman'; COLOR: rgb(51,51,51); FONT-SIZE: 16px; PADDING-TOP: 0px">@'slave_ip</a>' identified<wbr style="PADDING-BOTTOM: 0px; LINE-HEIGHT: 28px; BACKGROUND-COLOR: rgb(247,252,255); MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT-FAMILY: 'Times new roman'; COLOR: rgb(51,51,51); FONT-SIZE: 16px; PADDING-TOP: 0px">by'<wbr style="PADDING-BOTTOM: 0px; LINE-HEIGHT: 28px; BACKGROUND-COLOR: rgb(247,252,255); MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT-FAMILY: 'Times new roman'; COLOR: rgb(51,51,51); FONT-SIZE: 16px; PADDING-TOP: 0px">repl_password';

备份

FLUSH<wbr style="PADDING-BOTTOM: 0px; LINE-HEIGHT: 28px; BACKGROUND-COLOR: rgb(247,252,255); MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT-FAMILY: 'Times new roman'; COLOR: rgb(51,51,51); FONT-SIZE: 16px; PADDING-TOP: 0px">TABLES<wbr style="PADDING-BOTTOM: 0px; LINE-HEIGHT: 28px; BACKGROUND-COLOR: rgb(247,252,255); MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT-FAMILY: 'Times new roman'; COLOR: rgb(51,51,51); FONT-SIZE: 16px; PADDING-TOP: 0px">WITH<wbr style="PADDING-BOTTOM: 0px; LINE-HEIGHT: 28px; BACKGROUND-COLOR: rgb(247,252,255); MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT-FAMILY: 'Times new roman'; COLOR: rgb(51,51,51); FONT-SIZE: 16px; PADDING-TOP: 0px">READ<wbr style="PADDING-BOTTOM: 0px; LINE-HEIGHT: 28px; BACKGROUND-COLOR: rgb(247,252,255); MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT-FAMILY: 'Times new roman'; COLOR: rgb(51,51,51); FONT-SIZE: 16px; PADDING-TOP: 0px">LOCK;

reset master

不要退出终端，再开启一个终端

linux :cd /var/lib #mysql数据目录

tar zcvf mysql.tar.gz mysql

scp mysql.tar.gz root@slave_ip :/var/lib

mysl>unlock tables;

SHOW<wbr style="PADDING-BOTTOM: 0px; LINE-HEIGHT: 28px; BACKGROUND-COLOR: rgb(247,252,255); MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT-FAMILY: 'Times new roman'; COLOR: rgb(51,51,51); FONT-SIZE: 16px; PADDING-TOP: 0px">MASTER<wbr style="PADDING-BOTTOM: 0px; LINE-HEIGHT: 28px; BACKGROUND-COLOR: rgb(247,252,255); MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT-FAMILY: 'Times new roman'; COLOR: rgb(51,51,51); FONT-SIZE: 16px; PADDING-TOP: 0px">STATUS;

4,salve

tarzxvfmysql.tar.gz

chown-Rmysql:mysqlmysql

编辑/etc/my.cnf
server-id=2
log-bin=mysql-bin
master-host=192.168.0.1
master-user=slave
master-password=111111
master-port=3306
replicate-do-db=test#需要备份的数据库名
replicate-ignore-db=mysql#忽略的数据库
master-connect-retry=60#如果从服务器发现主服务器断掉，重新连接的时间差(秒)
log-slave-updates#这个参数一定要加上，否则不会给更新的记录些到二进制文件里
slave-skip-errors#是跳过错误，继续执行复制操作

验证连接MASTER
#mysql-h192.168.0.1-uslave-ppassword
mysql>showgrantsforslave@192.168.0.2;
在SLAVE上设置同步
设置连接MASTERMASTER_LOG_FILE为主库的File，MASTER_LOG_POS为主库的Position
mysql>slavestop;
mysql>CHANGEMASTERTOMASTER_HOST='192.168.0.1',MASTER_USER='slave',MASTER_PASSWORD='111111',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=106;
启动SLAVE服务