mysql 配置主备数据库_mysql数据库安装、主备配置流程

1、mysql数据库安装需要注意操作系统版本和数据库版本的匹配关系(试验安装最新版本，系统centos 7)

通过yum list mysql查看试用于操作系统的版本

[root@db-mysql02 mysql]# yum list mysql

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

Could not retrieve mirrorlist http://mirrorlist.centos.org/?release=7&arch=x86_64&repo=os&infra=stock error was

12: Timeout on http://mirrorlist.centos.org/?release=7&arch=x86_64&repo=os&infra=stock: (28, 'Operation too slow. Less than 1000 bytes/sec transferred the last 30 seconds')

* base: mirrors.shu.edu.cn

* extras: mirrors.shu.edu.cn

* updates: mirror.bit.edu.cn

Error: No matching Packages to list

注：如果查不到可安装版本，请先安装mysql yum源

yum localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm

2、安装MySQL

yum install mysql-community-server

3、启动mysql:

service mysqld start

注：此处可能会报错，提示初始化库失败，建议查看/etc/my.cnf查看log日志位置，查看具体原因，通常将库文件/mysql/下文件删除，重新引导即可完成

4、查看临时密码

grep 'temporary password' /var/log/mysqld.log

5、登录数据库

mysql -uroot -p

6、修改数据库密码

ALTER USER ‘root'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码';

由于数据库限制密码长度和复杂度，修改完成后可通过以下配置解除限制

show global variables like 'validate_password%';

set global validate_password.length=1;

set global validate_password.policy=0;

7、配置用于远程登录的账号

CREATE USER 'mysql'@'%' IDENTIFIED BY 'hadoop';

GRANT ALL ON *.* TO 'mysql'@'%';

flush privileges;

8、下面启动主备配置

先是master节点：

mysql> CREATE USER 'replicator'@'%' IDENTIFIED BY 'TileLowNilWife';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl’@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

配置my.cnf

log_bin = mysql-bin

server_id = 10

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

......some content not shown

重启库

service mysqld restart

配置slave库my.cnf

[mysqld]

server_id = 20

log_bin = mysql-bin

log_slave_updates = 1

relay_log = mysql-relay-bin

read_only = 1

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.soc

重启库service mysqld restart

登录slave库配置

>stop slave;

>change master to master_host='db-mysql01',master_user='repl',master_password='Root_12root';

>start slave;

测试，完成