如何在CentOS 7上安装MariaDB数据库

准备工作

安装jemalloc

jemalloc的源码网址是: https://github.com/jemalloc/jemalloc

下载release版:

wget https://github.com/jemalloc/jemalloc/releases/download/5.0.1/jemalloc-5.0.1.tar.bz2
//也可以用镜像网址
wget http://d.3651it.com/soft/jemalloc/jemalloc-5.0.1.tar.bz2

编译安装:

tar -xjf jemalloc-5.0.1.tar.bz2
cd jemalloc-5.0.1
./configure
make
make install

设置动态链接库为系统共享,由于libjemalloc.so文件在/usr/local/lib下,可以将此目录设置为动态链接库搜索目录

ln -s /usr/local/lib/libjemalloc.so.2 /usr/lib64/libjemalloc.so.1
echo '/usr/local/lib' > /etc/ld.so.conf.d/local.conf
ldcondif

删除安装文件

cd ..
rm -rf jemalloc-5.0.1
rm -rf jemalloc-5.0.1.tar.bz2

使用二进制包安装MariaDB

  1. 下载对应二进制版本

    查看glibc版本,如果大于2.14,选glibc_214版本

    getconf -a | grep GNU_LIBC_VERSION
    
    //下载
    wget http://mirrors.ctyun.cn/MariaDB/mariadb-10.2.5/bintar-linux-glibc_214-x86_64/mariadb-10.2.5-linux-glibc_214-x86_64.tar.gz
    
  2. 添加mysql用户及其组,并设置数据存储目录

    groupadd mysql 
    useradd -g mysql -M -s /bin/false mysql 
    //添加数据库存储目录 
    mkdir -p /data/mysql
    chown -R mysql:mysql /data/mysql
    
  3. 安装MariaDB

    tar xzvf mariadb-10.2.5-linux-glibc_214-x86_64.tar.gz -C /usr/local/
    ln -s /usr/local/mariadb-10.2.5-linux-glibc_214-x86_64 /usr/local/mysql
    sed -i 's@executing mysqld_safe@executing mysqld_safe\nexport LD_PRELOAD=/usr/local/lib/libjemalloc.so@' /usr/local/mysql/bin/mysqld_safe
    

设置my.cnf

    cat > /etc/my.cnf << EOF
    [client]
    port = 3306
    socket = /tmp/mysql.sock
    default-character-set = utf8mb4

    [mysqld]
    port = 3306
    socket = /tmp/mysql.sock

    basedir = /usr/local/mysql
    datadir = /data/mysql
    pid-file = /data/mysql/mysql.pid
    user = mysql
    bind-address = 0.0.0.0
    server-id = 1

    init-connect = 'SET NAMES utf8mb4'
    character-set-server = utf8mb4

    skip-name-resolve
    #skip-networking
    back_log = 300

    max_connections = 1000
    max_connect_errors = 6000
    open_files_limit = 65535
    table_open_cache = 128 
    max_allowed_packet = 4M
    binlog_cache_size = 1M
    max_heap_table_size = 8M
    tmp_table_size = 16M

    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    sort_buffer_size = 8M
    join_buffer_size = 8M
    key_buffer_size = 4M

    thread_cache_size = 8

    query_cache_type = 1
    query_cache_size = 8M
    query_cache_limit = 2M

    ft_min_word_len = 4

    log_bin = mysql-bin
    binlog_format = mixed
    expire_logs_days = 7 

    log_error = /data/mysql/mysql-error.log
    slow_query_log = 1
    long_query_time = 1
    slow_query_log_file = /data/mysql/mysql-slow.log

    performance_schema = 0

    #lower_case_table_names = 1

    skip-external-locking

    default_storage_engine = InnoDB
    innodb_file_per_table = 1
    innodb_open_files = 500
    innodb_buffer_pool_size = 64M
    innodb_write_io_threads = 4
    innodb_read_io_threads = 4
    innodb_thread_concurrency = 0
    innodb_purge_threads = 1
    innodb_flush_log_at_trx_commit = 2
    innodb_log_buffer_size = 2M
    innodb_log_file_size = 32M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 90
    innodb_lock_wait_timeout = 120

    bulk_insert_buffer_size = 8M
    myisam_sort_buffer_size = 8M
    myisam_max_sort_file_size = 10G
    myisam_repair_threads = 1

    interactive_timeout = 28800
    wait_timeout = 28800

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [myisamchk]
    key_buffer_size = 8M
    sort_buffer_size = 8M
    read_buffer = 4M
    write_buffer = 4M
    EOF

如果物理内存1.5G-2.5G

    sed -i 's@^thread_cache_size.*@thread_cache_size = 16@' /etc/my.cnf
    sed -i 's@^query_cache_size.*@query_cache_size = 16M@' /etc/my.cnf
    sed -i 's@^myisam_sort_buffer_size.*@myisam_sort_buffer_size = 16M@' /etc/my.cnf
    sed -i 's@^key_buffer_size.*@key_buffer_size = 16M@' /etc/my.cnf
    sed -i 's@^innodb_buffer_pool_size.*@innodb_buffer_pool_size = 128M@' /etc/my.cnf
    sed -i 's@^tmp_table_size.*@tmp_table_size = 32M@' /etc/my.cnf
    sed -i 's@^table_open_cache.*@table_open_cache = 256@' /etc/my.cnf

如果物理内存2.5G-3.5G

    sed -i 's@^thread_cache_size.*@thread_cache_size = 32@' /etc/my.cnf
    sed -i 's@^query_cache_size.*@query_cache_size = 32M@' /etc/my.cnf
    sed -i 's@^myisam_sort_buffer_size.*@myisam_sort_buffer_size = 32M@' /etc/my.cnf
    sed -i 's@^key_buffer_size.*@key_buffer_size = 64M@' /etc/my.cnf
    sed -i 's@^innodb_buffer_pool_size.*@innodb_buffer_pool_size = 512M@' /etc/my.cnf
    sed -i 's@^tmp_table_size.*@tmp_table_size = 64M@' /etc/my.cnf
    sed -i 's@^table_open_cache.*@table_open_cache = 512@' /etc/my.cnf

如果物理内存3.5G以上

    sed -i 's@^thread_cache_size.*@thread_cache_size = 64@' /etc/my.cnf
    sed -i 's@^query_cache_size.*@query_cache_size = 64M@' /etc/my.cnf
    sed -i 's@^myisam_sort_buffer_size.*@myisam_sort_buffer_size = 64M@' /etc/my.cnf
    sed -i 's@^key_buffer_size.*@key_buffer_size = 256M@' /etc/my.cnf
    sed -i 's@^innodb_buffer_pool_size.*@innodb_buffer_pool_size = 1024M@' /etc/my.cnf
    sed -i 's@^tmp_table_size.*@tmp_table_size = 128M@' /etc/my.cnf
    sed -i 's@^table_open_cache.*@table_open_cache = 1024@' /etc/my.cnf

安装基础数据库

cd /usr/local/mysql
./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

将mariadb自带命令放入$PATH

echo "export PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile
source !$

添加service文件

cat > /usr/lib/systemd/system/mysql.service << EOF
[Unit]
Description=MySQL DBMS

[Service]
LimitNOFILE=10000
Type=simple
User=mysql
Group=mysql
PIDFile=/data/mysql/mysql.pid
ExecStart=/usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql
ExecStop=/bin/kill -9 $MAINPID
Restart=always
PrivateTmp=false

[Install]
WantedBy=multi-user.target
Alias=mysql.service
EOF

启动

systemctl start  mysql.service

加入自启动

systemctl enable mysql.service

删除没有密码的帐号,设置root密码,删除test数据库

mysql_secure_installation

将mariadb的动态链接库共享至系统链接库

echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mariadb.conf
ldconfig

删除安装文件

rm -rf mariadb-10.2.5-linux-glibc_214-x86_64.tar.gz