MySQL实战演练

原创

已于 2024-08-27 09:40:01 修改 · 474 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2024-08-22 01:34:06 首次发布

一.linux中安装MySQL

1.安装依赖性

[root@mysql1 ~]# yum  install cmake gcc-c++ openssl-devel ncurses-devel.x86_64 -y
[root@mysql1 ~]# yum install libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm -y

2.下载并解压源码包

[root@mysql1 ~]# tar zxf mysql-boost-5.7.44.tar.gz 
[root@mysql1 ~]# cd mysql-5.7.44/

3.源码编译安装mysql

[root@mysql1 mysql-5.7.44]# cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \                 #指定安装路径
-DMYSQL_DATADIR=/data/mysql \                             #指定数据目录
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \                #指定套接字文件
-DWITH_INNOBASE_STORAGE_ENGINE=1 \                        #指定启用INNODB存储引擎，默认
用myisam
-DWITH_EXTRA_CHARSETS=all \                               #扩展字符集
-DDEFAULT_CHARSET=utf8mb4 \                               #指定默认字符集
-DDEFAULT_COLLATION=utf8mb4_unicode_ci \                  #指定默认校验字符集
-DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/       #指定c++库依赖

[root@mysql1 mysql-5.7.44]# make -j4  && make install

二.MySQL基础配置

1.生成启动脚本

[root@mysql-node1 mysql]# useradd -s /sbin/nologin -M mysql
[root@mysql-node1 mysql]# mkdir /data/mysql -p
[root@mysql-node1 mysql]# chown mysql.mysql -R /data/mysql

[root@mysql-node1 mysql]# cd support-files/
[root@mysql-node1 support-files]# cp mysql.server /etc/init.d/mysqld

2.生成配置文件

[root@mysql-node1 support-files]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql                #指定数据目录
socket=/data/mysql/mysql.sock      #指定套接字
symbolic-links=0                   #数据只能存放到数据目录中，禁止链接到数据目录

3.修改环境变量

[root@mysql-node1 support-files]# vim ~/.bash_profile

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

[root@mysql-node1 support-files]# source ~/.bash_profile

4.数据库初始化建立mysql基本数据

[root@mysql-node1 ~]# mysqld --user mysql --initialize
[root@mysql-node1 ~]# /etc/init.d/mysqld start
[root@mysql-node1 ~]# chkconfig mysqld on

5.数据库安全初始化

[root@mysql-node1 ~]# mysql_secure_installation

6.测试

[root@mysql-node1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.44 Source distribution

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

三.mysql的主从复制

1.配置master

(1).添加服务器ID

[root@mysql-node1 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=1

[root@mysql-node1 ~]# /etc/init.d/mysqld restart

(2).进入数据库配置用户权限

[root@mysql-node1 ~]# mysql -p123456

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'liu';   #生成专门用来做复制的用
户，此用户是用于slave端做认证用

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';   #对这个用户进行授权

mysql> SHOW MASTER STATUS;                           #查看master的状态
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      595 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

(3).查看二进制日志

[root@mysql-node1 ~]# cd /data/mysql/
[root@mysql-node1 mysql]# mysqlbinlog mysql-bin.000001 -vv

2.配置salve

(1).添加服务器ID

[root@mysql-node2 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2

[root@mysql-node2 ~]# /etc/init.d/mysqld restart

(2).进入数据库配置

[root@mysql-node2 ~]# mysql -p123456

mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10',MASTER_USER='repl',MASTER_PASSWORD='liu',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=595;
mysql> start slave;
mysql>  SHOW SLAVE STATUS\G;

(3).测试：主服务器添加数据，从服务器查看

mysql> CREATE DATABASE liu;

mysql> CREATE TABLE liu.userlist (
    -> username varchar(20) not null,
    -> password varchar(50) not null
    -> );
mysql> INSERT INTO liu.userlist VALUE ('user1','123');
mysql> SELECT * FROM liu.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+

3.当有数据时添加salve2

先完成基础配置，再做以下操作

(1).添加服务器ID

[root@mysql-node3 ~]# vim /etc/my.cnf

[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=3

[root@mysql-node3 ~]# /etc/init.d/mysqld restart

(2).从master节点备份数据并传给slave2

[root@mysql-node1 ~]# mysqldump -uroot -pliu liu > liu.sql
[root@mysql-node1 ~]# scp liu.sql root@172.25.254.30:/mnt/

(3).利用master节点中备份出来的lee.sql在slave2中拉平数据

[root@mysql-node3 ~]# mysql -uroot -p123456 -e "create database liu;"
[root@mysql-node3 mnt]# mysql -uroot -p123456 liu < liu.sql
[root@mysql-node3 mnt]# mysql -uroot -p123456 -e "select * from liu.userlist;"
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+

(4).配置slave2的slave功能

[root@mysql-node3 ~]# mysql -uroot -p123456
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',MASTER_PASSWORD='liu', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1240;
mysql> start slave;
mysql> SHOW SLAVE STATUS\G;

(5).测试

[root@mysql-node1 ~]# mysql -uroot -p123456 -e "INSERT INTO liu.userlist VALUES('user2','123');"

[root@mysql-node2 ~]# mysql -uroot -p123456 -e 'select * from liu.userlist;'
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
+----------+----------+

[root@mysql-node3 ~]# mysql -uroot -p123456 -e 'select * from liu.userlist;'
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
+----------+----------+

4.延迟复制

延迟复制时用来控制 sql 线程的，和 i/o 线程无关，这个延迟复制不是 i/o 线程过段时间来复制， i/o 是正常工作的，是日志已经保存在 slave 端了，那个 sql 要等多久进行回放。

在 slave 端配置

mysql> STOP SLAVE SQL_THREAD;
mysql> CHANGE MASTER TO MASTER_DELAY=30;
mysql> CHANGE MASTER TO MASTER_DELAY=30;
mysql> SHOW SLAVE STATUS\G;

最低0.47元/天解锁文章