[toc] 从今天开始MySQL相关方面的东西 今天是关于MySQL的安装 系统为 redhat 7.4 数据库为MySQL 8.0.19 ## 1. 目录规划 | 目录名称 | 参数名称 | 路径地址| | --------- | --------- | ---------| | 安装目录 | basedir|/usr/local/mysql| | 数据文件目录 | datadir |/data/mysql/data| | 临时文件目录 | tmpdir |/data/mysql/tmp| | socket文件目录 |socket|/data/mysql/data/mysql.sock| | bin日志文件目录| log_bin|/datalog/mysql/binlog| | relay日志文件目录| relay_log |/datalog/mysql/relaylog| ## 2. MySQL 8.0 下载 [https://dev.mysql.com/downloads/mysql/](https://dev.mysql.com/downloads/mysql/) [image:884 size:orig] [image:885 size:orig] ## 3. 新建用户及目录 `/usr/sbin/groupadd -g 105 mysql` `/usr/sbin/useradd -u 105 -g mysql mysql` `echo "mysql123" |passwd mysql --stdin` ## 4. 新建目录 ``` mkdir -p /data/mysql/software mkdir -p /usr/local/mysql mkdir -p /data/mysql/data mkdir -p /datalog/mysql/binlog mkdir -p /datalog/mysql/relaylog chown -R mysql:mysql /usr/local/mysql mkdir -p /data/mysql/tmp chown -R mysql:mysql /data/mysql chown -R mysql:mysql /datalog/mysql/ ``` ## 5. 配置环境变量 ``` su - mysql vim ~/.bash_profile export MYSQL_HOME=/usr/local/mysql export PATH=$HOME/bin:$MYSQL_HOME/bin:$PATH export LD_LIBRARY_PATH=$MYSQL_HOME/lib:$LD_LIBRARY_PATH source ~/.bash_profile ``` ## 6. 建立配置文件 8.0的参数和5.7的参数有些不一样 - expire_logs_days参数被废弃，使用binlog_expire_logs_seconds参数代替 - innodb_undo_tablespaces参数被废弃，8.0会根据innodb_page_size自动建立 - innodb_file_format，innodb_file_format_check，innodb_file_format_max，innodb_large_prefix参数被废弃并移除了 - query_cache_type和query_cache_size参数被废弃 我们这里建立 /etc/my.cnf ``` [mysql] socket = /data/mysql/data/mysql.sock [mysqld] #-----------------MySQL Basic Setting-----------------# server-id = 1921680101 port = 3306 user = mysql pid-file = mysql.pid character_set_server = utf8mb4 default_storage_engine = InnoDB skip_name_resolve = 1 lower_case_table_names = 1 explicit_defaults_for_timestamp = 1 open_files_limit = 65535 max_connections = 1000 max_connect_errors = 100000 basedir = /usr/local/mysql datadir = /data/mysql/data tmpdir = /data/mysql/tmp socket = /data/mysql/data/mysql.sock join_buffer_size = 64M tmp_table_size = 64M max_allowed_packet = 32M read_buffer_size = 16M read_rnd_buffer_size = 32M sort_buffer_size = 32M log_error_verbosity=2 log_timestamps=SYSTEM #-----------------MySQL Log Setting-----------------# log_error = mysql-error.log log_bin = /datalog/mysql/binlog/mysql-bin.log slow_query_log_file = mysql-slow.log relay_log = /datalog/mysql/relaylog/mysql-relay.log log_slave_updates = 1 sync_binlog = 1 relay_log_recovery = 1 binlog_format = row binlog_expire_logs_seconds = 604800 slow_query_log = 1 long_query_time = 2 log_queries_not_using_indexes = 1 log_throttle_queries_not_using_indexes = 10 log_slow_admin_statements = 1 log_slow_slave_statements = 1 min_examined_row_limit = 1000 #-----------------MySQL Replication Setting-----------------# master_info_repository = TABLE relay_log_info_repository = TABLE gtid_mode = on enforce_gtid_consistency = 1 binlog_rows_query_log_events = 1 #-----------------MySQL InnoDB Setting-----------------# innodb_page_size = 16384 innodb_buffer_pool_size = 400M innodb_data_file_path = ibdata1:1G:autoextend innodb_buffer_pool_instances = 8 innodb_file_per_table = 1 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 5 innodb_io_capacity = 800 innodb_io_capacity_max = 2000 innodb_flush_method = O_DIRECT #innodb_file_format = Barracuda #innodb_file_format_max = Barracuda innodb_flush_neighbors = 1 innodb_log_file_size = 2G innodb_log_buffer_size = 16777216 innodb_print_all_deadlocks = 1 innodb_strict_mode = 1 innodb_sort_buffer_size = 67108864 #-----------------MySQL semi Replication Setting-----------------# #plugin_dir = /usr/local/mysql/lib/plugin #plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #loose_rpl_semi_sync_master_enabled = 1 #loose_rpl_semi_sync_slave_enabled = 1 #loose_rpl_semi_sync_master_timeout = 5000 ``` **修改my.cnf权限** `chown mysql:mysql /etc/my.cnf` ## 7. 依赖包检查 ``` libaio-0.3.107-10.el6.x86_64 libaio-devel-0.3.107-10.el6.x86_64 lvm2-2.02.143-7.el6_8.1.x86_64 ``` ## 8. 卸载以前版本 如果有以前版本的MySQL ，则需要先下载 如只有lib库则可以不用动 RedHat Enterprise Linux 6适用】 ``` # rpm -qa|grep -i mysql mysql-libs-5.1.71-1.el6.x86_64 qt-mysql-4.6.2-26.el6_4.x86_64 mysql-5.1.71-1.el6.x86_64 mysql-server-5.1.71-1.el6.x86_64 #yum remove mysql-5.1.71-1.el6.x86_64 qt-mysql-4.6.2-26.el6_4.x86_64 mysql-libs-5.1.71-1.el6.x86_64 mysql-server-5.1.71-1.el6.x86_64 ``` 【RedHat Enterprise Linux 7适用】 ``` # rpm -qa|grep -i mariadb* mariadb-server-5.5.52-1.el7.x86_64 mariadb-libs-5.5.52-1.el7.x86_64 mariadb-embedded-5.5.52-1.el7.x86_64 mariadb-test-5.5.52-1.el7.x86_64 mariadb-bench-5.5.52-1.el7.x86_64 mariadb-5.5.52-1.el7.x86_64 mariadb-devel-5.5.52-1.el7.x86_64 mariadb-embedded-devel-5.5.52-1.el7.x86_64 # yum remove -y mariadb* ``` ## 9. 解压文件 ### 9.1 解压安装文件 ``` su - mysql tar xzvf mysql-8.0.19-el7-x86_64.tar.gz -C /usr/local/mysql/ --strip-components=1 ``` ### 9.2 配置服务文件 这里我们使用systectl来管理数据库的启停 root用户 ``` vi /usr/lib/systemd/system/mysqld.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 PIDFile=/data/mysql/data/mysqld.pid # Disable service start and stop timeout logic of systemd for mysqld service. TimeoutSec=0 # Execute pre and post scripts as root PermissionsStartOnly=true # Needed to create system tables #ExecStartPre=/usr/bin/mysqld_pre_systemd # Start main service ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/mysql/data/mysqld.pid $MYSQLD_OPTS #注意这里要加上 --daemonize # Use this to switch malloc implementation #EnvironmentFile=-/etc/sysconfig/mysql # Sets open_files_limit LimitNOFILE = 65536 Restart=on-failure RestartPreventExitStatus=1 PrivateTmp=false ``` ## 10. 初始化数据库 接下来我们初始化MySQL ``` # su - mysql $cd /usr/local/mysql $/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql ``` 初始化过程中可以通过error文件查看进度 `tail -f /data/mysql/data/mysql-error.log` ## 11. 启动数据库 ``` root用户 systemctl start mysqld ``` 后续可以使用如下命令启停数据库 `systemctl stop mysqld` `systemctl start mysqld` `systemctl restart mysqld` 设置MySQL自启动 `systemctl enable mysqld` ## 12. 连接数据库 默认密码在error文件中有 `cat /data/mysql/data/mysql-error.log |grep password` 使用如下命令连接 `mysql -S /data/mysql/data/mysql.sock -uroot -ppassword ` 第一次登陆需要修改密码 ``` alter user 'root'@'localhost' identified with mysql_native_password by 'password'; flush privileges; ``` 连接后的安全性设置参见MySQL常见命令 好了 MySQL的 Linux 安装就先讲到这里 后续内容尽情期待