MySQL-安装数据库（Linux）

1. 安装前环境配置

1）关闭防火墙

-- service版本
service iptables stop
chkconfig off iptables

-- systemctl版本
systemctl stop firewalld.service
systemctl disable firewalld.service

 

2）关闭selinux

# 禁用selinux，配置/etc/sysconfig/selinux，修改SELINUX项为disabled
if [[ "$(getenforce)" = "Enforcing" ]]; then
    cp /etc/selinux/config{,_$(date +%Y%m%d)} && setenforce 0 && sed -i  "/^(SELINUX=.*)/c\#/1\nSELINUX=disable" /etc/selinux/config
fi

 

3）配置操作系统句柄参数限制

cat > /etc/security/limits.d/99-mysql.conf <<-EOF
mysql   soft    nproc   65536
mysql   hard    nproc   65536
mysql   soft    nofile  65536
mysql   hard    nofile  65536
EOF

 

4）创建mysql系统用户

groupadd -r mysql
useradd -r -g mysql -c "Mysql server" -s /sbin/nologin -M mysql

 

2. 软件安装

2.1 安装系统依赖包

rpm -q --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" gcc gcc-c++ make cmake ncurses-devel libtool zlib-devel bison libaio

yum -y install  gcc gcc-c++ make cmake ncurses-devel libtool zlib-devel bison libaio

2.2 安装MySQL软件

1）rpm包安装

rpm -ivh mysql-commercial-common-5.7.27-1.1.el7.x86_64.rpm mysql-commercial-libs-5.7.27-1.1.el7.x86_64.rpm mysql-commercial-libs-compat-5.7.27-1.1.el7.x86_64.rpm mysql-commercial-server-5.7.27-1.1.el7.x86_64.rpm mysql-commercial-client-5.7.27-1.1.el7.x86_64.rpm mysql-commercial-devel-5.7.27-1.1.el7.x86_64.rpm

# lib路径：/usr/lib64/mysql
# 默认数据目录：/var/lib/mysql/
# 相关命令　　　/usr/bin(mysqladmin mysqldump等命令)
# 配置文件　　　/usr/share/mysql（mysql.server命令及配置文件）

2）二进制分发包安装

tar xf mysql-advanced-5.6.14-linux-glibc2.5-i686.tar.gz -C /usr/local/mysql
cd /usr/local/mysql
mv mysql-advanced-5.6.14-linux-glibc2.5-i686 mysql3308
cd mysql3308
mkdir -p {logs,conf}
mkdir -p /mydata/mysql3308
cp -p support-files/my-medium.cnf conf/my.cnf
cp -p support-files/mysql.server conf/mysqld3308
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /data/mysqldata/
chmod -R 750 /usr/local/mysql
chmod -R 700 /mydata/mysql3308

3) 源码编译安装方式

tar -xf mysql-boost-5.7.28.tar.gz
cd mysql-boost-5.7.28
mkdir debug
cd debug
cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql3308 \
-DMYSQL_DATADIR=/mydata/mysql3308 \
-DSYSCONFDIR=/usr/local/mysql3308/conf \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_READLINE=1  \
-DMYSQL_UNIX_ADDR=/usr/local/mysql3308/logs/mysql3308.sock \
-DMYSQL_TCP_PORT=3308 \
-DENABLED_LOCAL_INFILE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_PAM=ON \
-DWITH_SSL=bundled \
-DWITH_ZLIB=system \
-DWITH_BOOST=boost 

make --jobs $(grep -ci processor /proc/cpuinfo) && make --jobs $(grep -ci processor /proc/cpuinfo) install

3. 初始化

-- 版本低于5.6
# cd /usr/loca/mysql3308/
# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql3308 --datadir=/mydata/mysql3308
-- mysql 5.7 ( --initialize表示默认生成密码, --initialize-insecure 表示不生成密码 )
# ${MYSQL_HOME}/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --basedir=${MYSQL_HOME} --datadir=${MYSQL_DATA} --initialize

4. 启动服务

1) 参数文件（my.cnf/my.ini）配置

-- 示例

[client]
default-character-set = utf8
socket = /usr/local/mysql57/logs/mysql.sock

[mysqld]
basedir = /usr/local/mysql57
datadir = /usr/local/mysql57/data
log-error = /usr/local/mysql57/logs/err.log
pid-file = /usr/local/mysql57/logs/mysqld.pid
socket = /usr/local/mysql57/logs/mysql.sock
port = 3308
default-storage-engine = INNODB
character-set-server = utf8
collation-server = utf8_general_ci

binlog_format = ROW
log_bin = /usr/local/mysql57/data/binlogs/b3308
expire_logs_days = 3
max_binlog_size = 100m

max_connect_errors = 5
max_connections = 1000
max_user_connections = 1000
wait_timeout = 1000
open_files_limit = 1024
local-infile = 0
skip_name_resolve = ON

server_id = 3308001
master_info_repository=TABLE
relay_log_info_repository=TABLE

innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
log_timestamps=SYSTEM

# undo tablespace
innodb_undo_tablespaces=3 #设置为3个
innodb_undo_logs=128 #默认128个
innodb_undo_directory = ./

# gtid_mode
gtid_mode=on
enforce_gtid_consistency=on

log_slave_updates = ON
sync_binlog=1
relay_log = /usr/local/mysql57/data/binlogs/s3308

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

 

 

2) 配置服务

-- service 版本
ln -s /usr/local/mysql/mysql3308/conf/mysqld3308 /etc/rc.d/init.d/mysqld3308
chmod 755 /etc/rc.d/init.d/mysqld3308
cd /etc/rc.d/init.d/
chkconfig --add mysqld3308 
chkconfig --list |grep mysqld

-- systemctl 版本
ln -s /usr/local/mysql/mysql3308/conf/my.cnf /et/my.cnf
vi /usr/lib/systemd/system/mysql3308.service

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/usr/local/mysql57/logs/mysqld.pid
TimeoutSec=0
PermissionsStartOnly=true
ExecStart=/usr/local/mysql57/bin/mysqld --daemonize --pid-file=/usr/local/mysql57/logs/mysqld.pid $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILE = 10000
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false

 

3) 启动服务
# 方法1
/usr/loca/mysql/mysql3308/bin/mysqld_safe --defaults-file=/etc/my.cnf

# 方法2
systemctl start mysql3308
or
service mysql3308 start

 

5. 服务启动后配置

1) 修改root密码

mysql> use mysql;
mysql> update user set password=password('root') where user='root';
mysql> delete from user where user!='root' or host!='localhost';
mysql> flush privileges;

2）用户授权

# 远程访问授权
mysql> grant all privileges on *.* to 'root'@'192.168.10.%' IDENTIFIED BY 'root' with grant option;
mysql> grant all privileges on *.* to 'root'@'node' IDENTIFIED BY 'root' with grant option;
mysql> FLUSH PRIVILEGES;

6. 删除旧版本rpm方式安装的软件(可选)

# 检查
rpm -qa |grep -E "mariadb|mysql"
# 若存在，则卸载，
rpm -e --nodeps mariadb-libs