Linux环境安装Mariadb（欧拉）

一、安装Mariadb

1.方式一：源码离线安装

1.1 上传mariadb tar包和相关依赖包 

1.2 编写mariadb安装脚本

vi install_07_mariadb.sh 

#!/bin/bash

######################
# mariadb10 安装脚本 #
######################

WORK_PATH=/home/data/install_pkg
TMP_PATH=$WORK_PATH/tmp
PKG_PATH=$WORK_PATH/mariadb

if [ "$1" = "" ];
then
    echo -e "\033[0;31m 未输入操作系统类型 \033[0m  \033[0;34m {ubuntu|centos} \033[0m"
    exit 1
fi
LINUX_DISTRIBUTION=$1

#检查安装目录
if [ ! -d $PKG_PATH ];
then
    echo -e "${PKG_PATH} 缺失，结束进程"
    exit 1
fi

if [ ! -d $TMP_PATH ];
then
    mkdir -p $TMP_PATH
fi

#检查libaio
#echo `whereis libaio.so.1`

IS_LIBAIO=`echo \`whereis libaio.so.1\` | grep "/"`
if [ "$IS_LIBAIO" = "" ];
then
    echo -e "libaio.so.1缺失，尝试安装"
    cd $PKG_PATH
    if [ "$LINUX_DISTRIBUTION" = "ubuntu" ];
    then
        # ubuntu
        dpkg -i *.deb
    else
        # centos
        rpm -ivh libaio-0.3.109-13.el7.x86_64.rpm
        rpm -ivh libaio-devel-0.3.109-13.el7.x86_64.rpm
    fi
    #rpm -ivh libaio-0.3.109-13.el7.x86_64.rpm
    #rpm -ivh libaio-devel-0.3.109-13.el7.x86_64.rpm
    IS_LIBAIO_2=`echo \`whereis libaio.so.1\` | grep "/"`
    if [ "$IS_LIBAIO_2" = "" ];
    then
        echo -e "libaio安装失败，请手动安装"
        exit 1
    fi
    echo -e "libaio安装完成，继续执行"
fi
#attention: 需要root用户执行，安装目录在/usr/local/mariadb下

# 1.判断当前用户是否为root用户
if [ $(id -u) != "0" ]; then
    echo "error: you must be root run this script"
    exit 1
fi

# 2.定义变量
WKDIR=$(pwd)
INSTALL_HOME="/home/data"
MYSQL_HOME="$INSTALL_HOME/mariadb"
MYSQL_DATA="$MYSQL_HOME/data"
[ -d $INSTALL_HOME ] || mkdir -p $INSTALL_HOME

# 3. 检验安装目录
if [ -d $MYSQL_HOME ];then
    echo "error:your system have installed mysql"
    exit 1
fi

# 4. 卸载系统自带数据库
rpm -qa|grep mysql > /dev/null 2>&1
if [ $? -eq 0 ];then
    rpm -qa|grep mysql|xargs yum -y remove
fi
rpm -qa|grep mariadb > /dev/null 2>&1
if [ $? -eq 0 ];then
    rpm -qa|grep mariadb|xargs yum -y remove
fi

# 5. 开始安装
echo "start installing mysql"
#解压
echo "start tar..."
cd $TMP_PATH
cp $PKG_PATH/mariadb-10.3.35-linux-systemd-x86_64.tar.gz ./
tar -zxf ./mariadb-10.3.35-linux-systemd-x86_64.tar.gz -C ./
mv ./mariadb-10.3.35-linux-systemd-x86_64 $MYSQL_HOME

echo "tar complete"
[ -d $MYSQL_DATA ] || mkdir -p $MYSQL_DATA

# 创建用户
id mysql > /dev/null 2>&1
if [ $? -ne 0 ];then
    useradd -s /sbin/nologin -M mysql
fi

# 创建配置文件及日志目录
mkdir -p $MYSQL_HOME/{etc,logs,tmp}

# 更改mysql目录所属用户和组
chown -R mysql:mysql $MYSQL_HOME
echo "initializing mysql"

# 初始化mysql数据库
cd $MYSQL_HOME
./scripts/mysql_install_db --basedir=$MYSQL_HOME --datadir=$MYSQL_DATA --user=mysql

# 创建配置文件
cat >$MYSQL_HOME/etc/my.cnf<<EOF
[mysqld]
user = mysql
port = 3306
basedir = /home/data/mariadb
datadir = /home/data/mariadb/data
socket = /tmp/mysql.sock
pid-file = /home/data/mariadb/tmp/mysqld.pid
character-set-server = utf8
collation-server = utf8_unicode_ci
max_connections = 1000
log-error = /home/data/mariadb/logs/mysqld.log
EOF

# 注册服务
cat >/usr/lib/systemd/system/mysqld.service<<EOF
[Unit]
Description=MYSQL Server
Documentation=man:mysqld(8)
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
TimeoutSec=0
PermissionStartOnly=true
ExecStart=/home/data/mariadb/bin/mysqld --defaults-file=/home/data/mariadb/etc/my.cnf
LimitNOFILE = 5000
Restart=on-failure
PrivateTmp=false
EOF

# 配置环境变量
cat > /etc/profile.d/mysql.sh<<EOF
export PATH=$PATH:/home/data/mariadb/bin
EOF

# 使配置的环境变量生效
source /etc/profile.d/mysql.sh

# 启动服务
systemctl daemon-reload && systemctl enable mysqld && systemctl start mysqld

#配置环境变量
echo 'export MARIADB_HOME=/home/data/mariadb' >> /etc/profile
echo 'export PATH=$MYSQL_HOME/bin:$PATH' >> /etc/profile
source /etc/profile

# 安装完成后执行，配置root密码
echo -e "安装即将完成，即将进行root密码的配置，运行 $MYSQL_HOME/bin/mysql_secure_installation --basedir=$MYSQL_HOME"
echo -e "按回车键继续……"
read enter_continue
sleep 1;
$MYSQL_HOME/bin/mysql_secure_installation --basedir=$MYSQL_HOME

2.方式二：Docker离线安装

2.1 加载镜像

docker load < mariadb.tar 

或

docker load -i mariadb.tar

2.2 查看是否已有镜像

docker images

2.3 运行容器

docker run -d --name mariadb --privileged=true -p 3306:3306

 -e MYSQL_ROOT_PASSWORD=密码  -e TZ="Asia/Shanghai"

-v /etc/mysql/mysql.conf.d/mysql.cnf:/etc/mysql/mysql.conf.d/mysql.cnf   #可要可不要

-v /home/data/mariadb/data:/var/lib/mysql mariadb:10.2

ps： -v 设置容器目录/var/lib/mysql 映射到本地目录/home/data/mariadb/data

2.4 修改容器为自启动 

docker container update --restart=always <容器id>

3.数据库安装常见问题

 3.1 mariadb启动失败

解决：排查下面2个文件是否有问题，无问题则执行第三个语句

    ①.vim /home/data/mariadb/etc/my.cnf

    ②.vim /usr/lib/systemd/system/mysqld.service

    ③./home/data/mariadb/bin/mysqld --defaults-file=/home/data/mariadb/etc/my.cnf

华为欧拉系统不支持：systemctl status mysqld 

3.2 error while loading shared libraries：libncurses.so.5

解决方案：

3.3 出现-bash :mysql :command not found

由于系统默认会查找/usr/bin下的命令，mysql没有在这个目录下，故报错,建立软链接可以解决。

即：ln -s /usr/local/mariadb/bin/mysql /usr/bin/  

3.4 密码错误

[root@localhost shell]# mysql -uroot -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

说明密码错误

二、数据库配置

1.设置不区分大小写

编辑etc/my.cnf文件：

#设置不区分大小写

lower_case_table_names=1

2.连接数据库

mysql -uroot -p密码 

3.创建用户并授权

create user 'test'@'%' identified by 'password';

grant all privileges on *.* to 'test'@'%' identified by 'password';

flush privileges;

select host,user,password from mysql.user;

4.数据库设置白名单

 注意：上面的%替换成具体的ip，且需要删除原先含有%的用户。

select host,user,password from mysql.user;

create user 'wuxx'@'10.128.XX.164' identified by 'mariadb@4.112';

grant all privileges on *.* to 'wxx@'10.128.XX.164' identified by 'mariadb@4.112';

flush privileges;

5.mariadb配置主从数据库 

#配置主库vi /etc/my.cnf

server_id=112

log_bin=mariadb-bin

#配置从库

vi /etc/my.cnf

server_id=72

relay_log=relay_bin

##==== 主库操作 start =====#

#使用root@localhost登录主库

 mysql -uroot -pmariadb

#创建从库访问主库账号，用于同步数据库

grant all on *.* to slaveadmin@'从库ip' identified by 'password';

#查看主库状态

MariaDB [nardf]> show master status;

+--------------------+----------+--------------+------------------+

| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+--------------------+----------+--------------+------------------+

| mariadb-bin.000003 |      389|              |                  |

+--------------------+----------+--------------+------------------+

1 row in set (0.000 sec)

##==== 主库操作 end =====#

##==== 从库操作 start =====#

#使用root@localhost登录从库

 mysql -uroot -p密码

#设置从库

change master to master_host='主库ip', master_user='slaveadmin',master_port=8011, master_password='password',master_log_file='mariadb-bin.000001',master_log_pos=389;

start slave;

show slave status\G

##==== 从库操作 end =====#

5.1 常见问题

5.1.1 master position一直变化

解决：备份数据时加锁，不让position变化：

flush tables with read lock;

释放锁：

unlock tables;

5.1.2 如果报如下错误，可以执行stop slave;reset slave;

5.1.3 同步主库数据失败

解决方案：

1）从机停止slave ：stop slave;

2）到master机器登录mysql

3）马上到slave执行：change master to master_log_file='mariadb-bin.000004',master_log_pos=389;

即：

MariaDB [(none)]> change master to master_log_file='mariadb-bin.000004',master_log_pos=389;

Query OK, 0 rows affected (0.008 sec)

MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.001 sec)

4）出现下图表示能正常同步

5.1.4主从复制停止了 

问题分析：

1）在主库执行：show master status;

2）在从库执行show slave status;

-- 查看具体报错原因（Error 'Duplicate entry '3' for key 'PRIMARY'' on query. Default database: ''. Query: 'INSERT INTO rpum_sys_dev.syst_config (config_key,config_val,config_group,`scope`,REMARK,config_name)

VALUES ('passwd_expired','1','passwd','GLOBAL','密码是否设置过期时间，1开启，0关闭','密码是否设置过期时间，1开启，0关闭')'）

3）恢复同步

stop slave;

set Global sql_Slave_SKIP_counter =1; --跳过一个事务

start slave;

6.将测试环境数据导到生产库

mysql -uroot -pMariadb@192.168 < patr_device_202312261323.sql（文件不能太大，超过4万条不可以） 

7. 把主库数据复制到从库

先退出数据库：exit

mysqldump -uroot -pmariadb --all-databases > /home/db.sql

scp db.sql 从库ip:/home/db.sql

到从库服务器上，先检查是否存在db.sql，存在则：

 mysql -uroot -p密码 < db.sql

8.客户端（例如dbeaver）连不上可能原因 

8.1 查看端口是否被监听到了，监听到说明服务启动了

netstat -anlp | grep 3306 

或netstat -ntlp ｜grep 3306

或lsof -i:3306 （list opened files，查看端口被谁占用）

netstat 说明：只列出监听中的连接netstat -tnl ， -p 选项查看进程信息，-a 列出所有当前的连接， -t 选项列出 TCP 协议的连接，-u 选项列出 UDP 协议的连接，-n 选项禁用域名解析功能， -l 选项列出正在监听的套接字 ，-i 网络接口信息

8.2 防火墙端口没开

firewall-cmd --zone=public --add-port=3306/tcp --permanent

firewall-cmd --reload

8.3排查网络是否暴露了端口(检测网络层面)

在本机telnet：

telnet 10.128.XXX.XXX 3306

9.常用mariadb数据库语句 

9.1 系统

查看数据库时间：

show variables like '%time_zone%';     

mariadb查锁表语句：

show open tables where in_use >0;

select *from information_schema.INNODB_LOCKS;

查看数据库事务隔离级别：

select @@tx_isolation

查询mariadb数据库版本、连接、线程、进程：

show variables like '%version%';

show variables like '%connection%';

show status like '%Threads%';

show full processlist ;

kill杀掉sleep进程

select group_concat(concat('kill ',id) separator ';') as cmd from information_schema.PROCESSLIST p

where user='disc' and COMMAND ='Sleep' and db ='discipline' and Host like '10.128.73.XXX%';

9.2 sql优化

9.2.1用exists 和not exists 代替in和not in 或用 join代替