centos主从数据库

最新推荐文章于 2021-12-12 06:50:42 发布

West Asia

最新推荐文章于 2021-12-12 06:50:42 发布

阅读量323

点赞数

分类专栏：主从数据库文章标签： mysql centos linux

本文链接：https://blog.csdn.net/weixin_45737719/article/details/109278877

版权

主从数据库专栏收录该内容

1 篇文章 1 订阅

订阅专栏

需求：两台centos虚拟机，网络是通的。
配置yum源则需要：CentOS-7-x86_64-DVD-1511.iso镜像，使用网络yum源则不需要

修改主机名（两个节点）

//修改主机名，也可以永久修改命令：hostnamectl  set-hostname name

[root@localhost ~]# hostname mysql1
[root@localhost ~]# bash

[root@localhost ~]# hostname mysql2
[root@localhost ~]# bash

关闭防火墙及selinux服务（两个节点）

[root@mysql1 ~]# systemctl stop firewalld
[root@mysql1 ~]# setenforce 0

配置hosts文件映射（两个节点）

[root@mysql2 ~]# vi /etc/hosts
//添加以下两行
192.168.174.10 mysql1
192.168.174.20 mysql2
~

配置yum源并安装数据库(两个节点)可以ftp

注：使用网络yum源，则不需要配置，只需跳到下面下载数据库服务的步骤

[root@mysql1 ~]# mkdir /opt/centos
[root@mysql1 ~]# mount CentOS-7-x86_64-DVD-1511.iso /opt/centos/
mount: /dev/loop0 is write-protected, mounting read-only

[root@mysql1 ~]# cd /etc/yum.repos.d/
[root@mysql1 yum.repos.d]# ls 
CentOS-Base.repo  CentOS-Debuginfo.repo  CentOS-Media.repo    CentOS-Vault.repo
CentOS-CR.repo    CentOS-fasttrack.repo  CentOS-Sources.repo

[root@mysql1 yum.repos.d]# mv * /opt/
[root@mysql1 yum.repos.d]# vi local.repo
[centos]
name=centos
baseurl=file:///opt/centos
enabled=1
gpgcheck=0

清空缓存，查看yum源是否可用（两个节点）

[root@mysql1 yum.repos.d]# yum clean all
Loaded plugins: fastestmirror
Cleaning repos: centos
Cleaning up everything
[root@mysql1 yum.repos.d]# yum repolist
Loaded plugins: fastestmirror
centos                                                   | 3.6 kB     00:00     
(1/2): centos/group_gz                                     | 155 kB   00:00     
(2/2): centos/primary_db                                   | 2.8 MB   00:00     
Determining fastest mirrors
repo id                              repo name                            status
centos                               centos                               3,723
repolist: 3,723

下载数据库并初始化数据库并配置主从数据库（两个节点）

注：主节点先配置

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

[root@mysql1 ~]# systemctl start mariadb //启动数据库，不启动是没办法初始化的
[root@mysql1 ~]# mysql_secure_installation   //初始化数据库
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

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!

[root@mysql1 ~]# vi /etc/my.cnf
//在mysqld下添加以下两行
[mysqld]
log_bin=mysql-bin   //记录操作日志
server_id=10		//数据库集群中的每个节点id不同，普通写成id的最后一段

重启数据库，并进入数据库

[root@mysql1 ~]# systemctl restart mariadb //重启数据库
[root@mysql1 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server

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

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


//在主节点上创建一个user用户连接点mysql2，并赋予从节点同步主节点数据库的权限。
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)]>

配置从节点


[root@mysql2 ~]# systemctl start mariadb    //启动mariadb服务
[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): 
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!

[root@mysql2 ~]# vi /etc/my.cnf
[mysqld]
log_bin=mysql-bin
server_id=20

[root@mysql2 ~]# systemctl restart mariadb  //配置好文件记得重启数据库

//进入数据库
[root@mysql2 ~]# mysql -u root -p000000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.65-MariaDB MariaDB Server

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

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

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

//查看从节点服务状态
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave  status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql1
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 527
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 811
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes  //这两处都是yes
            Slave_SQL_Running: Yes  //这两处都是yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 527
              Relay_Log_Space: 1107
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
1 row in set (0.00 sec)

主节点创建数据库，数据表，插入数据


MariaDB [(none)]> create database mytest;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use mytest;
Database changed
MariaDB [mytest]> create table exam(
    -> id int primary key AUTO_INCREMENT,
    -> name varchar(10) not null,
    -> math double,
    -> chinese double,
    -> english double);
Query OK, 0 rows affected (0.00 sec)

MariaDB [mytest]> insert into exam values
    -> (null,'polo',56,80,75),(null,'rose',82,65,90),(null,'emilly',69,78,50),(null,'elle',78,70,84),(null,'jack',90,85,92);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [mytest]> select * from exam；
    -> ;
ERROR 1146 (42S02): Table 'mytest.exam；' doesn't exist
MariaDB [mytest]> select * from exam;
+----+--------+------+---------+---------+
| id | name   | math | chinese | english |
+----+--------+------+---------+---------+
|  1 | polo   |   56 |      80 |      75 |
|  2 | rose   |   82 |      65 |      90 |
|  3 | emilly |   69 |      78 |      50 |
|  4 | elle   |   78 |      70 |      84 |
|  5 | jack   |   90 |      85 |      92 |
+----+--------+------+---------+---------+
5 rows in set (0.00 sec)

从节点验证查看

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytest             |
| performance_schema |
| test               |
| test1              |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]> use mytest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [mytest]> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| exam             |
+------------------+
1 row in set (0.00 sec)

MariaDB [mytest]> select * from exam;
+----+--------+------+---------+---------+
| id | name   | math | chinese | english |
+----+--------+------+---------+---------+
|  1 | polo   |   56 |      80 |      75 |
|  2 | rose   |   82 |      65 |      90 |
|  3 | emilly |   69 |      78 |      50 |
|  4 | elle   |   78 |      70 |      84 |
|  5 | jack   |   90 |      85 |      92 |
+----+--------+------+---------+---------+
5 rows in set (0.00 sec)

MariaDB [mytest]>

可以看到主数据库中刚创建的库，表，信息，验证从数据库的复制功能成功。

到此主从数据库完成。

有可能遇到的问题：
Slave_SQL_Running: No
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后，事务回滚造成的
一般是事务回滚造成的：
解决办法：
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
第一次操作完毕之后，输入命令showslave status\G ，发现还是没变化，再次操作一次，应该就好。

West Asia

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
2
评论
centos主从数据库

需求：两台centos虚拟机centos修改主机名（两个节点）[root@localhost ~]# hostname mysql1[root@localhost ~]# bash[root@localhost ~]# hostname mysql2[root@localhost ~]# bash关闭防火墙及selinux服务（两个节点）[root@mysql1 ~]# systemctl stop firewalld[root@mysql1 ~]# setenforce 0配置h
复制链接

扫一扫