2017年5月5日星红桉liunx动手实践mysql 主主双机热备

第一步：安装mysql

可参考http://www.runoob.com/mysql/mysql-install.html

心得：

rpm -ivh mysql-community-common-5.7.10-1.el6.x86_64.rpm --force --nodeps
rpm -ivh mysql-community-libs-5.7.10-1.el6.x86_64.rpm --force --nodeps
rpm -ivh mysql-community-client-5.7.10-1.el6.x86_64.rpm --force --nodeps
rpm -ivh mysql-community-server-5.7.10-1.el6.x86_64.rpm --force --nodeps

启动
service mysqld start

/etc/init.d/mysqld/etc/init.d/mysqld start

5.7装好mysql

mysql -u用户名 -p密码

默认安装后是登录不进去的，需要跳过登录，直接进入

mysqld_safe --user=mysql --skip-grant-tables --skip-networking&mysql /**进入mysql交互窗口*/

mysql>UPDATE mysql.user SET authentication_string=PASSWORD('root') where USER='root'; /*5.7之后的版本user表中没有password字段*/
否则：mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';

切记：

只要修改mysql 系统文件就要刷新权限 mysql> flush privileges;

mysql> exit;

第二步：

修改mysql编码，同时修改密码安全级别：
vi /etc/my.cnf

# reset pass level
validate_password_policy = 0
validate_password_length = 1
validate_password_mixed_case_count = 0
validate_password_number_count = 0
validate_password_special_char_count = 0

character_set_server=utf8
default-storage-engine=INNODB
collation-server=utf8_general_ci

[client]
default-character-set=utf8

授权
mysql> grant all privileges on *.* TO 'root'@'localhost' identified by 'root' with grant option;本地
mysql>GRANT ALL PRIVILEGES ON *.* TO ' root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;远程
刷新权限
flush privileges;

第三步：

两台服务器 A服务器（10.69.37.75）

　　　　　 B服务器（10.69.37.122）

service mysqld stop

A服务器（10.69.37.75）

在A服务器的mysql配置文件中添加 server_id=1 log_bin=mysql-bin binlog_ignore_db=information_schema,performance_schema,mysql

vi /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
# binlog_format=mixed
# log-bin=mysql-bin
# server-id=1
# binlog-do-db=starv_hlw,starv_boss
# binlog-ignore-db=mysql
# replicate-do-db=starv_hlw,starv_boss
# replicate-ignore-db=mysql
# log-slave-updates
# sync_binlog=1
# auto_increment_increment=2
# auto_increment_offset=1

#双机热备主要是下面三行

server_id=1
log_bin=mysql-bin
binlog_ignore_db=information_schema,performance_schema,mysql ##排除的数据库，也就是不需要同步的数据库

#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/var/lib/mysql
datadir=/data1/mysqldata/mysql
socket=/var/lib/mysql/mysql.sock
#socket=/data1/mysqldata/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# reset pass level
validate_password_policy = 0
validate_password_length = 1
validate_password_mixed_case_count = 0
validate_password_number_count = 0
validate_password_special_char_count = 0

character_set_server=utf8
default-storage-engine=INNODB
collation-server=utf8_general_ci

[client]
default-character-set=utf8

退出my.cnf

启动 service mysqld start

mysql -root -proot

mysql>　　

mysql> change master to master_host='10.69.37.75',master_port=3306,master_user='root',master_password='root';

如果不成功

　　首先在A服务器上连接B服务器数据库看是否能够连接进去

　　mysql -h10.69.37.122 -uroot -proot

　　如果不能进入就需要查看B服务的 servie mysqld status

如果成功