db主从数据库

db主从数据库

主从同步：(备份，负载（读））

第一步：数据库的初始化，修改配置文件，定义server-id（所有节点），开启二进制日志功能binlog（主节点、主库）

第二步：授权，复制权限（主库）

第三步：从库连接主库

第四步：查询从库状态

第五步：验证主从同步的结果

提前做：

1.修改主机名（mysql1、mysql2）

[root@compute ~]# hostnamectl set-hostname mysql1
[root@compute ~]# bash

[root@compute ~]# hostnamectl set-hostname mysql1
[root@compute ~]# bash

2.写域名解析文件

[root@mysql1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.111 mysql1
10.10.10.222 mysql2

[root@controller ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.111 mysql1
10.10.10.222 mysql2

3.关闭Selinux

[root@mysql1 ~]# cat /etc/selinux/config 

# This file controls the state of SELinux on the system.
# SELINUX=permissive
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three two values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected. 
# mls - Multi Level Security protection.
SELINUXTYPE=targeted 

[root@mysql2 ~]# cat /etc/selinux/config 

# This file controls the state of SELinux on the system.
# SELINUX=permissive
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three two values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected. 
# mls - Multi Level Security protection.
SELINUXTYPE=targeted 

4.关闭防火墙

[root@mysql1 ~]# systemctl stop firewalld

[root@mysql2 ~]# systemctl stop firewalld

5.安装mariadb服务

[root@mysql1 ~]# yum install -y mariadb mariadb-server 

[root@mysql2 ~]# yum install -y mariadb mariadb-server 

6.启动服务

[root@mysql1 ~]# systemctl start mariadb 
[root@mysql1 ~]# systemctl enable mariadb 

[root@mysql2 ~]# systemctl start mariadb
[root@mysql2 ~]# systemctl enable mariadb

7.初始化数据库

mysql1

[root@mysql1 ~]# mysql_secure_installation 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
  SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

mysql2

[root@mysql2 ~]# mysql_secure_installation 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
  SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

8.在mysql1上

[root@mysql1 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# This group is read by the server
#
[mysqld]
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]                                      #加上这句
log-bin = mysql-bin						      #加上这句
server-id =222(这个222是IP地址的最后一位)       #加上这句

9.在mysql2上

[root@mysql2 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# This group is read by the server
#
[mysqld]
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-storage-engine = innodb
innodb_file_per_table
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8
max_connections=10000

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d     								#加上这句
[mysqld]					  								#加上这句
server-id =111 (这个111是IP地址的最后一位) 				    #加上这句

10.重启服务

[root@mysql1 ~]# systemctl restart mariadb

[root@mysql2 ~]# systemctl restart mariadb

11.登陆mysql1

[root@mysql1 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.20-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant all privileges  on *.* to root@'%' identified by "000000";
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant replication slave on *.* to 'user'@'mysql2' identified by '000000';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye

11.登陆mysql2

[root@mysql2 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.20-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant all privileges on . to root@'%' identified by "000000";
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> change master to master_host='mysql1',master_user='user',master_password='000000';
Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

12.验证mysql1

MariaDB [(none)]> show databases;    #查看数据库
+--------------------+
| Database   |
+--------------------+
| information_schema |
| mysql  |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> create database test;   #创建数据库
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use test;    #使用数据库 
Database changed

MariaDB [test]>  create table company(id int not null primary key,name varchar(50),addr varchar(255));  #创建表
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]>  create table company(id int not null primary key,name varchar(50),addr varchar(255));  #给表插入数据
Query OK, 0 rows affected (0.01 sec)

13.查看mysql2

MariaDB [(none)]> show databases;
MariaDB [(none)]> use test;
MariaDB [(none)]> show tables;
MariaDB [(none)]> select * from company;   #查询表中的数据
MariaDB [(none)]> drop table;     #删除表

完结，撒花！！