系统配置 创建用户和组
$ groupadd mysql $ useradd mysql -g mysql
创建数据目录
$ mkdir /service/data $ mkdir /service/binlog $ chown -R mysql:mysql /service/data $ chown -R mysql:mysql /service/binlog
配置参数文件(/etc/my.cnf)
[client] port = 3306 socket = /service/data/mysqld.sock [mysqld] ##BASIC CONFIGURATION server-id = 5220 port= 33006 socket = /service/data/mysqld.sock basedir = /usr/local/mysql datadir = /service/data skip_name_resolve = 1 default-storage-engine = INNODB character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci back_log = 500 wait_timeout = 600 interactive_timeout = 600 connect_timeout=120 plugin_load = validate_password.so symbolic-links = 0 lower_case_table_names = 1 local_infile=OFF skip_networking=OFF skip_show_database=OFF max_allowed_packet = 512M event_scheduler=OFF explicit_defaults_for_timestamp = 1 group_concat_max_len = 102400 log_error_verbosity=2 ##BINLOG CONFIGURATION log-bin = /service/binlog/mysql-bin log_bin_trust_function_creators = ON binlog_format = row binlog_cache_size = 4M max_binlog_cache_size = 1G max_binlog_size = 512M expire_logs_days = 20 ##LOG CONFIGURATION slow_query_log = 1 slow_query_log_file = /service/data/mysqlslow.log long_query_time = 1 log_slow_admin_statements = 1 log_queries_not_using_indexes = 1 log_throttle_queries_not_using_indexes = 10 log-error = /service/data/mysqld.log ##SESSION CONFIGURATION max_connections = 2000 max_user_connections = 2000 max_connect_errors = 100000 table_open_cache = 2048 table_definition_cache = 4096 table_open_cache_instances = 32 read_buffer_size = 16M read_rnd_buffer_size = 16M sort_buffer_size = 8M join_buffer_size = 8M tmp_table_size = 128M thread_cache_size = 64 thread_stack = 256K query_cache_type = 0 query_cache_size = 0 open_files_limit = 65535 max_prepared_stmt_count=1048576 ##GTID CONFIGURATION gtid-mode=on enforce-gtid-consistency=true log-slave-updates=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 binlog-checksum=CRC32 slave_allow_batching = 1 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 slave-parallel-workers=4 relay_log_purge = 1 relay_log_recovery = 1 ##INNODB CONFIGURATION innodb_buffer_pool_size = 140G #(65%-75% Physical memory) innodb_data_file_path = ibdata1:1G:autoextend innodb_buffer_pool_instances = 8 innodb_log_file_size = 512M innodb_log_buffer_size = 32M innodb_log_files_in_group = 3 innodb_lock_wait_timeout = 100 innodb_thread_concurrency = 1000 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT innodb_read_io_threads = 4 innodb_write_io_threads = 16 innodb_io_capacity = 4000 innodb_io_capacity_max = 10000 innodb_file_per_table = 1 innodb_strict_mode = 1 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_purge_batch_size = 32 innodb_change_buffering = all innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_lru_scan_depth = 4096 innodb_undo_logs = 128 innodb_undo_tablespaces = 3 innodb_flush_neighbors = 0 innodb_print_all_deadlocks = 1 innodb_sort_buffer_size = 67108864 innodb_stats_persistent_sample_pages = 64 innodb_online_alter_log_max_size=1G transaction_isolation = READ-COMMITTED sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" [mysql] prompt = "\u@\d \R:\m:\s " default-character-set = utf8mb4 no-auto-rehash
Limit配置
$ cat /etc/security/limits.conf mysql soft nproc 10240 mysql hard nproc 10240 mysql soft nofile 65535 mysql hard nofile 65535
关闭selinux
[root@t-luhxdb01-p-szzb ~]# vi /etc/selinux/config SELINUX=disabled [root@t-luhxdb01-p-szzb ~]# setenforce 0
关闭transparent_hugepage
$ echo never > /sys/kernel/mm/transparent_hugepage/enabled $ echo never > /sys/kernel/mm/transparent_hugepage/defrag $ echo "echo never > /sys/kernel/mm/transparent_hugepage/enabled" >> /etc/rc.local $ echo "echo never > /sys/kernel/mm/transparent_hugepage/defrag" >> /etc/rc.local
安装数据库 解压安装包
$ tar -xvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz $ mv mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysql $ chown -R root.mysql /usr/local/mysql
配置环境变量
$ echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile $ source /etc/profile
初始化数据库
$ mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/service/data
Tips: 初始化后随机root密码记录在mysql日志中
启动数据库
$ cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld $ /etc/init.d/mysqld start
修改root密码
$ mysql -uroot -p root@(none) 15:16> set password=password('NewPassword'); root@(none) 15:16> flush privileges;
多实例环境 创建不同的数据目录
[root@t-luhx02-v-szzb mysql]# mkdir /service/mysql/db1/data -p [root@t-luhx02-v-szzb mysql]# mkdir /service/mysql/db1/binlog -p [root@t-luhx02-v-szzb mysql]# mkdir /service/mysql/db2/binlog -p [root@t-luhx02-v-szzb mysql]# mkdir /service/mysql/db2/data -p [root@t-luhx02-v-szzb mysql]# chown -R mysql.mysql /service/mysql/
配置多实例my.cnf
[mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin log = /service/mysql/mysqld_multi.log user=multi_admin pass=ceshi@123 [mysqld33006] server_id=111 port=33006 datadir=/service/mysql/db1/data log_error=/servie/mysql/db1/data/mysqld.log log_bin=/service/mysql/db1/binlog/mysql-bin pid-file = /service/mysql/db1/data/mysqld.pid socket = /service/mysql/db1/data/mysql.sock [mysqld33007] server_id=222 port=33007 datadir=/service/mysql/db2/data log_error=/servie/mysql/db2/data/mysqld.log log_bin=/service/mysql/db2/binlog/mysql-bin pid-file = /service/mysql/db2/data/mysqld.pid socket = /service/mysql/db2/data/mysql.sock
模块名称必须以mysqld开头,并且会继承[mysqld]中的其它参数
初始化实例
mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/service/mysql/db1/data mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/service/mysql/db2/data
查看实例服务
[root@t-luhx02-v-szzb mysql]# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld33006 is not running MySQL server from group: mysqld33007 is not running
启动实例
[root@t-luhx02-v-szzb mysql]# mysqld_multi start 33006 [root@t-luhx02-v-szzb mysql]# mysqld_multi start 33007
创建multi管理用户
> create user 'multi_admin'@'localhost' identified by 'ceshi@123'; > grant shutdown on *.* to 'multi_admin'@'localhost'; > flush privileges;
停止实例
[root@t-luhx02-v-szzb mysql]# mysqld_multi stop 33006 [root@t-luhx02-v-szzb mysql]# mysqld_multi stop 33007