#=========1、MySQL====================================
#导入与还原数据库
mysqldump -uroot -p zabbix > /root/zabbix.sql
还原数据库
mysql -uroot -p zabbix < /root/zabbix.sql
#清理binlog
mysql -uroot -p
help purge
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';
#更改 root密码
service mysql stop
echo skip-grant-tables > /etc/my.conf
service mysql start
mysql
USR root;
UPDATE user SET Password = password ( '1q2w3e4r5t' ) WHERE User = 'root' ;
flush privileges ;
quit
vi /etc/my.conf
#skip-grant-tables
wq
#取消密码复杂性
SHOW VARIABLES LIKE 'vali%'; #查看是否启用了密码复杂性
[mysqld]
validate_password=off
#安装Precona
yum -y remove mysql-libs
yum install cronie cronie-anacron crontabs redhat-lsb-core sysstat
#yum -y install mysql-client mysql-server
#mysql config
#service mysql start
#chkconfig mysql on
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum install Percona-Server-shared-56 Percona-Server-client-56 Percona-Server-server-56
service mysqld start
chkconfig mysqld on #设置开机启动
MySQL -v #检测MySQL版本
#mysqladmin -u root passwd root 给root设置数据库密码
===# 主从复制========================================
#打开Master数据库 my.conf文件
vi /etc/my.conf
server_id=1
binlog_format=ROW
binlog_row_p_w_picpath=minimal
#设置需要备份的数据库
binlog-do-db=db_01
binlog-do-db=db_02
expire-logs-days=7 #保留7天的二进制日志
binlog_format=MIXED #设置binlog格式为MIXED
log-bin = mysql-bin #开启MYSQL二进制日志
wq
service mysql restart
mysql #进入mysql
use ebdb_trans; #使用ebdb_trans数据库
grant replication slave on *.* to 'backup'@'192.168.0.11'identified by '你的密码'; #设置slave IP和登陆权限
flush privileges;
flush tables with read lock;
reset master；
show master status\G; #查看file、position 并记录该值
exit
#备份master数据库到slave
mysqldump ebdb_trans > /root/ebdb_trans.sql
#切换到slave并恢复数据库
mysql ebdb_trans < /root/ebdb_trans
vi my.conf
server-id=2
replication-do-db=ebdb_trans #需要复制的数据库名称
slave-skip-errors=1032,1062,126,1114,1146,1048,1396 #自动跳过的错误代码，以防复制出错被中断
wq
mysql #指定主库信息
reset slave;
change master to master_host='192.168.0.8',master_user='backup',master_password='28270033',master_log_file='mysql-bin.000001',master_log_pos=120;
start slave; #启动slave
show slave status\G; #检查从库状态 （Slave_IO_Running: Yes Slave_SQL_Running: Yes）file
===========================================================================
常见问题：
1. Slave_IO_Running:no
（1查看 my.conf ---server-id 是否与master相同；
（2查看server_UUID是否与master相同,如相同则修改/var/lib/mysql/auto.conf
进入mysql
show variables like '%server_uuid%'
2. Slave_SQL_Running: no
停掉从库，并重新指定主库信息，最后启动slave
===========================================================================