mysql8.0.20 二进制安装

关闭selinux

1 2	sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config setenforce  0

 修改系统限制参数

1 2 3 4 5 6 7 8 9

cat >> /etc/security/limits.conf << EOF # ###custom # *           soft   nofile       20480 *           hard   nofile       65535 *           soft   nproc        20480 *           hard   nproc        65535 EOF

 修改内核参数

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

cat >>/etc/sysctl.conf <<"EOF" vm.swappiness=0 #增加tcp支持的队列数 net.ipv4.tcp_max_syn_backlog = 65535 #减少断开连接时 ，资源回收 net.ipv4.tcp_max_tw_buckets = 8000 net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_tw_recycle = 1 net.ipv4.tcp_fin_timeout = 10 #改变本地的端口范围 net.ipv4.ip_local_port_range = 1024 65535 #允许更多的连接进入队列 net.ipv4.tcp_max_syn_backlog = 4096  #对于只在本地使用的数据库服务器 net.ipv4.tcp_fin_timeout = 30 #端口监听队列 net.core.somaxconn=65535 #接受数据的速率 net.core.netdev_max_backlog=65535 net.core.wmem_default=87380 net.core.wmem_max=16777216 net.core.rmem_default=87380 net.core.rmem_max=16777216 EOF sysctl -p

 创建用户

1 2	groupadd mysql useradd -g mysql mysql -d /home/mysql -s /sbin/nologin

 创建相应的目录

1	mkdir -p /data/mysql/mysql_3306/{data,logs,tmp}

 创建配置文件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184

cat >/data/mysql/mysql_3306/my_3306.cnf <<"EOF" [client] port    = 3306 socket    = /data/mysql/mysql_3306/tmp/mysql_3306.sock [mysql] prompt="\u@\h \R:\m:\s [\d]> " no-auto-rehash [mysqld] user    = mysql port    = 3306 admin_address = 127.0.0.1 basedir    = /usr/local/mysql datadir    = /data/mysql/mysql_3306/data socket    = /data/mysql/mysql_3306/tmp/mysql_3306.sock pid-file = mysql_3306.pid character-set-server = utf8mb4 skip_name_resolve = 1 lower_case_table_names=1 注：在 MySQL 8 中，数据目录初始化之后，不再允许更改 lower_case_table_names = 1 的 值 #replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table=test.% replicate-wild-ignore-table=information_schema.% # Two-Master configure #server-1 #auto-increment-offset = 1 #auto-increment-increment = 2 #server-2                          #auto-increment-offset = 2 #auto-increment-increment = 2 # semi sync replication settings # #plugin_dir = /usr/local/mysql/lib/mysql/plugin #plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" plugin_dir = /usr/local/mysql/lib/plugin #官方版本的路径 plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #官方版本的路径 slave_parallel_workers = 4 slave_parallel_type = LOGICAL_CLOCK open_files_limit    = 65535 back_log = 1024 max_connections = 1024 max_connect_errors = 1000000 table_open_cache = 1024 table_definition_cache = 1024 table_open_cache_instances = 64 thread_stack = 512K external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1536 interactive_timeout = 600 wait_timeout = 600 tmp_table_size = 32M max_heap_table_size = 32M slow_query_log = 1 log_timestamps = SYSTEM slow_query_log_file = /data/mysql/mysql_3306/logs/slow.log log-error = /data/mysql/mysql_3306/logs/error.log long_query_time = 0.1 log_queries_not_using_indexes =1 log_throttle_queries_not_using_indexes = 60 min_examined_row_limit = 100 log_slow_admin_statements = 1 log_slow_slave_statements = 1 server-id = 3306 log-bin = /data/mysql/mysql_3306/logs/mysql-bin sync_binlog = 1 binlog_cache_size = 4M max_binlog_cache_size = 2G max_binlog_size = 1G binlog_expire_logs_seconds=2592000 master_info_repository = TABLE relay_log_info_repository = TABLE gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN' binlog_format = row binlog_row_image=FULL binlog_checksum = 1 relay_log_recovery = 1 relay-log-purge = 1 key_buffer_size = 32M read_buffer_size = 8M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 lock_wait_timeout = 3600 explicit_defaults_for_timestamp = 1 innodb_thread_concurrency = 0 innodb_sync_spin_loops = 100 innodb_spin_wait_delay = 30 #transaction_isolation = REPEATABLE-READ transaction_isolation = READ-COMMITTED #innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 2867M innodb_buffer_pool_instances = 4 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_data_file_path = ibdata1:1G:autoextend innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 32M innodb_log_file_size = 2G innodb_log_files_in_group = 3 innodb_max_undo_log_size = 4G innodb_undo_directory = /data/mysql/mysql_3306/undolog innodb_undo_tablespaces = 95 # 根据您的服务器IOPS能力适当调整 # 一般配普通SSD盘的话，可以调整到 10000 - 20000 # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_flush_sync = 0 innodb_flush_neighbors = 0 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_open_files = 65535 innodb_max_dirty_pages_pct = 50 innodb_flush_method = O_DIRECT innodb_lru_scan_depth = 4000 innodb_checksum_algorithm = crc32 innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 innodb_online_alter_log_max_size = 4G innodb_stats_on_metadata = 0 # some var for MySQL 8 log_error_verbosity = 3 innodb_print_ddl_logs = 1 binlog_expire_logs_seconds = 2592000 #innodb_dedicated_server = 0 innodb_status_file = 1 # 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快 innodb_status_output = 0 innodb_status_output_locks = 0 #performance_schema performance_schema = 1 performance_schema_instrument = '%memory%=on' performance_schema_instrument = '%lock%=on' #innodb monitor innodb_monitor_enable="module_innodb" innodb_monitor_enable="module_server" innodb_monitor_enable="module_dml" innodb_monitor_enable="module_ddl" innodb_monitor_enable="module_trx" innodb_monitor_enable="module_os" innodb_monitor_enable="module_purge" innodb_monitor_enable="module_log" innodb_monitor_enable="module_lock" innodb_monitor_enable="module_buffer" innodb_monitor_enable="module_index" innodb_monitor_enable="module_ibuf_system" innodb_monitor_enable="module_buffer_page" innodb_monitor_enable="module_adaptive_hash" validate_password_policy=LOW [mysqldump] quick max_allowed_packet = 32M [mysqld_safe] #malloc-lib=/usr/local/mysql/lib/jmalloc.so nice=-19 open-files-limit=65535 EOF

 修改目录权限

1	chown -R mysql.mysql /data/mysql/mysql_3306

 安装

1 2 3

tar -xvf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz mv mysql-8.0.20-linux-glibc2.12-x86_64 mysql chown -R mysql.mysql /data/mysql/mysql_3306

 初始化数据库

1	/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --initialize-insecure  --user=mysql &

 启动数据库

1	/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my_3306.cnf &

 首次登录方式

1	/usr/local/mysql/bin/mysql --socket=/data/mysql/mysql_3306/tmp/mysql_3306.sock

 快捷登陆

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

cat  >>/root/.bashrc <<"EOF" # alias mysql.3306.start="/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my_3306.cnf &" alias mysql.3306.stop="/usr/local/mysql/bin/mysqladmin -h127.0.0.1 -P 3306 -uroot -p'password' shutdown &" alias mysql.3306.login="/usr/local/mysql/bin/mysql -h127.0.0.1 -P 3306 -uroot -p'Password'" EOF source  /root/.bash_profile cat >>/etc/ld.so.conf <<"EOF" /usr/local/mysql/lib EOF ldconfig [root@db144 ~]# mysql.3306.login mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 14Server version: 8.0.15 MySQL Community Server - GPLCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> exitBye[root@db144 ~]#

ln -sf /data/mysql/mysql_3306/tmp/mysql_3306.sock /tmp/mysql.sock
ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5

mysql在安装后会创建一个root@locahost账户，并且把初始的密码放到了/var/log/mysqld.log文件中；

cat /var/log/mysqld.log | grep password
或
grep 'temporary password' /var/log/mysqld.log

## 登录，修改密码，修改登陆规则，刷新
$ mysql -uroot -p'初始密码？？？？'
mysql> $ ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '15144042132@163.Com';
mysql> $ use mysql;
mysql> $ update user set host='%' where host='localhost';
mysql> $ delete from user where host !='%';
mysql> $ flush privileges;
mysql> $ SHOW VARIABLES LIKE 'validate_password%';
mysql> $ set global validate_password.policy=0;
mysql> $ set global validate_password.length=4;
mysql> $ ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '1234qwer';
mysql> $ quit

#配置环境变量

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

   select host,user from user;
  update user set host='%' where user='root';
  create user 'usrabc'@'localhost' identified by 'usrabc';
 
 GRANT SELECT, INSERT, DELETE,UPDATE ON *.* TO 'usrabc'@'localhost';
  FLUSH PRIVILEGES;