mysql备份之主从和主主

今天来讲一下mysql服务器的主从备份环境的搭建和配置（主主其实是主从演变而来，即两台机器互为主从关系：你配了从服务器同步主服务器的日志文件，同时也配置了主服务器同步从服务器的日志文件，这样就形成了主主，即你无论在那台机器操作相应的数据变化都会映射到另外一台机器上）

设置主从要有以下几个步骤：

1、服务器IP地址规划和服务名规划以及mysql数据库的安装（安装有两种方式一种直接拿来官网的二进制包cpy到服务器指定目录进行配置一下，另外一个就是编译安装，以后另开博文详解）

172.16.0.66   mysql-master1

172.16.0.169  mysql-master2

我们这次统一安装mysql版本是5.7.9版本

2、配置已经安装好的mysql配置文件my.cnf

mysql-master1的配置如下：
[mysql]
default-character-set=utf8

[mysqld]
port=3306
basedir=/data/mysql5.7.9/
datadir=/data/mysqldata/
socket=/tmp/mysql.sock
character-set-server=utf8
user=mysql
server-id=1
log-bin=/data/mysqldata/mysql_logs/mysql-bin
log-bin-index=mysql-bin.index
relay-log=/data/mysqldata/mysql_logs/relay-bin
relay-log-index=relay-bin.index
auto-increment-offset=1
auto-increment-increment=2
binlog-ignore-db=mysql
replicate-ignore-db=mysql
sync_binlog=1
innodb_buffer_pool_size=8589934592
innodb_buffer_pool_instances=4
binlog_cache_size=2097152
query_cache_size=134217728
join_buffer_size=8388608
sort_buffer_size=8388608
max_connections=1000
max_allowed_packet=33554432
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/data/mysqldata/mysqld.log
pid-file=/data/mysqldata/mysqld.pid

mysql-master2的配置如下：
[mysql]
default-character-set=utf8

[mysqld]
port=3306
basedir=/data/mysql5.7.9/
datadir=/data/mysqldata/
socket=/tmp/mysql.sock
character-set-server=utf8
user=mysql
server-id=2
log-bin=/data/mysqldata/mysql_logs/mysql-bin
log-bin-index=mysql-bin.index
relay-log=/data/mysqldata/mysql_logs/relay-bin
relay-log-index=relay-bin.index
binlog-ignore-db=mysql
replicate-ignore-db=mysql 
log-slave-updates=on
sync_binlog=1
auto-increment-offset=2
auto-increment-increment=2
innodb_buffer_pool_size=8589934592
innodb_buffer_pool_instances=4
binlog_cache_size=2097152
query_cache_size=134217728
join_buffer_size=8388608
sort_buffer_size=8388608
max_connections=1000
max_allowed_packet=33554432
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/data/mysqldata/mysqld.log
pid-file=/data/mysqldata/mysqld.pid

3、创建设置的目录，给予权限，防火墙设置并重启mysql

mkdir -p /data/mysqldata/mysql_logs/
chown -R mysql:mysql /data/mysqldata

防火墙设置

vi /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
service iptables restart

service mysql restart

4、互为主从操作

所有操作都是命令行mysql -uroot -p 登录到mysql数据库后进行

mysql-master1操作：（添加进行主从备份的用户以及赋予其相应权限）

grant replication slave on *.* to 'repl'@'172.16.0.169' identified by 'repl';

select user,host,authentication_string from mysql.user;

flush  privileges ;

mysql-master2操作：（添加进行主从备份的用户以及赋予其相应权限）

grant replication slave on *.* to 'repl'@'172.16.0.66' identified by 'repl';

select user,host,authentication_string from mysql.user;

flush  privileges ;

mysql-master1操作：（设置备份主服务器和备份用户名密码，启动从服务器slave操作）

change master to  master_host='172.16.0.169', master_user='repl', master_password='repl';

start slave;

show slave status\G  出现两个YES，为配置正确

mysql-master2操作：（设置备份主服务器和备份用户名密码，启动从服务器slave操作）

change master to  master_host='172.16.0.66', master_user='repl', master_password='repl';

start slave;

show slave status\G  出现两个YES，为配置正确

以下是主从备份日常运维碰到的问题以及解决方案：

主从备份 出错 跳过出错日志

 set global sql_slave_skip_counter =1;

重新启动slave（一般我们遇到的问题是主服务器上运行了一个语句奔溃了，要么报错，要么从服务器没有设置好无法运行此语句，保险的做法就是先拿到出错的最后一条sql，自己手动的在两台服务器上测试一下，找到出错原因，解决掉然后设置 global sql_slave_skip_counter启动主从跳过来恢复）

启动binlog创建函数失败设置

当有 mysql本地或远程建立function或procedure时报上面的错误 

经试验是log_bin_trust_function_creators值为off导致 

log_bin_ trust_function_creators=1

show variables like 'log_bin_trust_function_creators'

查看数据库中有哪些自定义函数：
show function status\G
查看函数定义：
show create function function_name\G

修改密码方法：

然后登陆mysql

mysql -uroot -pnew_password

mysql> update mysql.user set

authentication_string= 

password('test_new2_password') where user= 'root';    mysql> FLUSH PRIVILEGES;

发生错误：Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
不能通过 '/tmp/mysql .sock'连到服务器，一些mysql 安装方法 将 mysql .sock放在/var/lib/mysql .sock或者其他的什么地方，你可以通过修改/etc/my.cnf文件来修正它，打开文件，可以看到如下的东东：
 　　[mysqld] 
　　socket=/var/lib/mysql.sock 
　　改一下就好了，但也会引起其他的问题，如mysql 程序连不上了，再加一点： 
　　[mysql ] 
　　socket=/tmp/mysql.sock 


rm -rf  mysql.sock
　　或者用这样的方法:
　　ln -s /var/lib/mysql/mysql.sock  /tmp/mysql.sock
/var/lib/mysql/mysql.sock
/tmp/mysql.sock

2、使用pgrep：
ps -aux|grep mysql
grep！pgrep的p表明了这个命令是专门用于进程查询的grep。
查看火狐的PID
$ pgrep firefox
1827
$kill -s 9 1827
ps -aux|grep mysql

杀掉mysq所有进程重启