环境:
1 [root@node ~]# cat /etc/redhat-release 2 CentOS Linux release 7.2.1511 (Core) 3 [root@node ~]# uname -a 4 Linux node 3.10.0-327.el7.x86_64 #1 SMP Thu Nov 19 22:10:57 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux 5 [root@node ~]#
软件版本:
1 [root@node ~]# cd tools/ 2 [root@node tools]# ls -l 3 total 320864 4 -rw-r--r-- 1 root root 328563044 May 6 16:35 mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz 5 [root@node tools]#
创建用于MySQL使用的用户和用户组及数据存放目录:
1 [root@node tools]# groupadd mysql 2 [root@node tools]# useradd -g mysql mysql -s /sbin/nologin 3 [root@node tools]# mkdir -p /data/mysql 4 [root@node tools]# chown -R mysql.mysql mysql 5 [root@node tools]# chown -R mysql.mysql /data/mysql/
解压MySQL二进制包,移到指定位置(/usr/local目录),并创建软链接
1 [root@node local]# ln -s mysql-5.6.40-linux-glibc2.12-x86_64 mysql 2 [root@node local]# chown -R mysql.mysql mysql 3 [root@node local]# chown -R mysql.mysql /data/mysql/ 4 [root@node local]#
调整配置文件my.cnf。
1 [client] 2 port = 3306 3 socket = /tmp/mysql.sock 4 default-character-set = utf8 5 [mysql] 6 default-character-set = utf8 7 [mysqld] 8 port = 3306 9 socket = /tmp/mysql.sock 10 basedir = /usr/local/mysql 11 datadir = /data/mysql 12 open_files_limit = 65535 13 back_log = 103 14 max_connections = 512 15 max_connect_errors = 100000 16 table_open_cache = 512 17 external-locking = FALSE 18 max_allowed_packet = 128M 19 sort_buffer_size = 2M 20 join_buffer_size = 2M 21 thread_cache_size = 51 22 query_cache_size = 32M 23 tmp_table_size = 96M 24 max_heap_table_size = 96M 25 slow_query_log = 1 26 slow_query_log_file = /data/mysql/slow.log 27 log-error = /data/mysql/error.log 28 long_query_time = 0.5 29 server-id = 2213306 30 log-bin = /data/mysql/mysql-bin 31 sync_binlog = 1 32 binlog_cache_size = 4M 33 max_binlog_cache_size = 128M 34 max_binlog_size = 1024M 35 expire_logs_days = 7 36 key_buffer_size = 32M 37 read_buffer_size = 1M 38 read_rnd_buffer_size = 16M 39 bulk_insert_buffer_size = 64M 40 character-set-server = utf8 41 default-storage-engine = InnoDB 42 binlog_format = row 43 #gtid_mode = on 44 #log_slave_updates = 1 45 interactive_timeout = 300 46 wait_timeout = 300 47 transaction_isolation = REPEATABLE-READ 48 innodb_buffer_pool_size = 143M 49 innodb_data_file_path = ibdata1:1024M:autoextend 50 innodb_flush_log_at_trx_commit = 1 51 innodb_log_buffer_size = 16M 52 innodb_log_file_size = 256M 53 innodb_log_files_in_group = 2 54 innodb_max_dirty_pages_pct = 50 55 innodb_file_per_table = 1 56 innodb_locks_unsafe_for_binlog = 0 57 [mysqldump] 58 quick 59 max_allowed_packet = 32M
MySQL5.7.x配置文件(仅供参考):
1 [client] 2 port = 3306 3 socket = /tmp/mysql.sock 4 5 [mysql] 6 prompt="\u@db \R:\m:\s [\d]> " 7 no-auto-rehash 8 9 [mysqld] 10 user = mysql 11 port = 3306 12 basedir = /usr/local/mysql 13 datadir = /data/mysql/ 14 socket = /tmp/mysql.sock 15 character-set-server = utf8mb4 16 skip_name_resolve = 1 17 open_files_limit = 65535 18 back_log = 1024 19 max_connections = 512 20 max_connect_errors = 100000 21 table_open_cache = 1024 22 table_definition_cache = 1024 23 thread_stack = 512K 24 external-locking = FALSE 25 max_allowed_packet = 32M 26 sort_buffer_size = 4M 27 join_buffer_size = 4M 28 thread_cache_size = 0 29 query_cache_size = 0 30 query_cache_type = 0 31 interactive_timeout = 600 32 wait_timeout = 600 33 tmp_table_size = 32M 34 slow_query_log =1 35 slow_query_log_file = /data/mysql/slow.log 36 log-error = /data/mysql/error.log 37 long_query_time = 0.5 38 server-id = 3306001 39 log-bin = /data/mysql/mysql-binlog 40 sync_binlog = 1 41 binlog_cache_size = 4M 42 max_binlog_cache_size = 1G 43 max_binlog_size = 1G 44 expire_logs_days = 7 45 master_info_repository = TABLE 46 relay_log_info_repository = TABLE 47 gtid_mode = on 48 enforce_gtid_consistency = 1 49 log_slave_updates 50 binlog_format = row 51 relay_log_recovery = 1 52 relay-log-purge = 1 53 key_buffer_size = 32M 54 read_buffer_size = 8M 55 read_rnd_buffer_size = 4M 56 bulk_insert_buffer_size = 64M 57 #plugin-load=AUDIT=libaudit_plugin.so 58 plugin-load=MCAFEE=libaudit_plugin.so 59 60 lock_wait_timeout = 3600 61 explicit_defaults_for_timestamp = 1 62 innodb_thread_concurrency = 0 63 innodb_sync_spin_loops = 100 64 innodb_spin_wait_delay = 30 65 transaction_isolation = REPEATABLE-READ 66 innodb_buffer_pool_size = 1024M 67 innodb_buffer_pool_instances = 8 68 innodb_buffer_pool_load_at_startup = 1 69 innodb_buffer_pool_dump_at_shutdown = 1 70 innodb_data_file_path = ibdata1:1G:autoextend 71 innodb_flush_log_at_trx_commit = 1 72 innodb_log_buffer_size = 32M 73 innodb_log_file_size = 2G 74 innodb_log_files_in_group = 2 75 innodb_io_capacity_max = 2000 76 innodb_io_capacity_max = 4000 77 innodb_flush_neighbors = 0 78 innodb_write_io_threads = 8 79 innodb_read_io_threads = 8 80 innodb_purge_threads = 4 81 innodb_page_cleaners = 4 82 innodb_open_files = 65535 83 innodb_max_dirty_pages_pct = 50 84 innodb_flush_method = O_DIRECT 85 innodb_lru_scan_depth = 4000 86 innodb_checksum_algorithm = crc32 87 innodb_lock_wait_timeout = 10 88 innodb_rollback_on_timeout = 1 89 innodb_print_all_deadlocks = 1 90 innodb_file_per_table = 1 91 innodb_online_alter_log_max_size = 4G 92 internal_tmp_disk_storage_engine = InnoDB 93 innodb_stats_on_metadata = 0 94 innodb_status_file = 1 95 innodb_status_output = 0 96 innodb_status_output_locks = 0 97 98 #performance_schema 99 100 performance_schema = 1 101 performance_schema_instrument = '%=on' 102 103 #innodb monitor 104 innodb_monitor_enable="module_innodb" 105 innodb_monitor_enable="module_server" 106 innodb_monitor_enable="module_dml" 107 innodb_monitor_enable="module_ddl" 108 innodb_monitor_enable="module_trx" 109 innodb_monitor_enable="module_os" 110 innodb_monitor_enable="module_purge" 111 innodb_monitor_enable="module_log" 112 innodb_monitor_enable="module_lock" 113 innodb_monitor_enable="module_buffer" 114 innodb_monitor_enable="module_index" 115 innodb_monitor_enable="module_ibuf_system" 116 innodb_monitor_enable="module_buffer_page" 117 innodb_monitor_enable="module_adaptive_hash" 118 119 [mysqldump] 120 quick 121 max_allowed_packet = 32M
初始化数据库(5.6):
1 [root@node scripts]# cd /usr/local/mysql/scripts/ 2 [root@node scripts]# pwd 3 /usr/local/mysql/scripts 4 [root@node scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/ --defaults-file=/etc/my.cnf --user=mysql 5 WARNING: The host 'node' could not be looked up with /usr/local/mysql/bin/resolveip. 6 This probably means that your libc libraries are not 100 % compatible 7 with this binary MySQL version. The MySQL daemon, mysqld, should work 8 normally with the exception that host name resolving will not work. 9 This means that you should use IP addresses instead of hostnames 10 when specifying MySQL privileges ! 11 12 Installing MySQL system tables...2018-05-07 02:08:51 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 13 2018-05-07 02:08:51 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap. 14 2018-05-07 02:08:51 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.40-log) starting as process 2902 ... 15 OK 16 17 Filling help tables...2018-05-07 02:08:54 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 18 2018-05-07 02:08:54 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap. 19 2018-05-07 02:08:54 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.40-log) starting as process 2924 ... 20 OK 21 22 To start mysqld at boot time you have to copy 23 support-files/mysql.server to the right place for your system 24 25 PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! 26 To do so, start the server, then issue the following commands: 27 28 /usr/local/mysql/bin/mysqladmin -u root password 'new-password' 29 /usr/local/mysql/bin/mysqladmin -u root -h node password 'new-password' 30 31 Alternatively you can run: 32 33 /usr/local/mysql/bin/mysql_secure_installation 34 35 which will also give you the option of removing the test 36 databases and anonymous user created by default. This is 37 strongly recommended for production servers. 38 39 See the manual for more instructions. 40 41 You can start the MySQL daemon with: 42 43 cd . ; /usr/local/mysql/bin/mysqld_safe & 44 45 You can test the MySQL daemon with mysql-test-run.pl 46 47 cd mysql-test ; perl mysql-test-run.pl 48 49 Please report any problems at http://bugs.mysql.com/ 50 51 The latest information about MySQL is available on the web at 52 53 http://www.mysql.com 54 55 Support MySQL by buying support/licenses at http://shop.mysql.com 56 57 WARNING: Found existing config file /usr/local/mysql/my.cnf on the system. 58 Because this file might be in use, it was not replaced, 59 but was used in bootstrap (unless you used --defaults-file) 60 and when you later start the server. 61 The new default config file was created as /usr/local/mysql/my-new.cnf, 62 please compare it with your file and take the changes you need. 63 64 WARNING: Default config file /etc/my.cnf exists on the system 65 This file will be read by default by the MySQL server 66 If you do not want to use this, either remove it, or use the 67 --defaults-file argument to mysqld_safe when starting the server 68 69 [root@node scripts]#
出现两个OK,说明初始化数据库成功。
初始化数据库并启动数据库(5.7):
[root@node ~]# cd /usr/local/mysql-5.7.26/ [root@node mysql-5.7.26]# cd bin/ [root@node bin]# ./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql [root@nodebin]# /etc/init.d/mysqld start Starting MySQL.. SUCCESS! [root@node01 bin]# [root@node bin]# netstat -lnupt|grep 3306 tcp6 0 0 :::3306 :::* LISTEN 19241/mysqld [root@node bin]# [root@node bin]# mysql -hlocalhost -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.26-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@db 18:53: [(none)]> update user set authentication_string=password('root') where user='root'; ERROR 1046 (3D000): No database selected root@db 18:53: [(none)]> use mysql Database changed root@db 18:53: [mysql]> update user set authentication_string=password('root') where user='root'; Query OK, 1 row affected, 1 warning (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 1 root@db 18:53: [mysql]> flush privileges; Query OK, 0 rows affected (0.00 sec) root@db 18:53: [mysql]>
接下来启动数据库:
1 [root@node scripts]# cd /usr/local/mysql/bin/ 2 [root@node bin]# ./mysqld_safe --defaults-file=/etc/my.cnf & 3 [1] 2950 4 [root@node bin]# 180507 02:13:27 mysqld_safe Logging to '/data/mysql/error.log'. 5 180507 02:13:27 mysqld_safe Starting mysqld daemon with databases from /data/mysql 6 7 [root@node bin]# 8 [root@node bin]# 9 [root@node bin]# ps xua|grep mysql 10 root 2950 0.7 0.0 113252 1616 pts/1 S 02:13 0:00 /bin/sh ./mysqld_safe --defaults-file=/etc/my.cnf 11 mysql 3583 4.5 30.3 1483272 567324 pts/1 Sl 02:13 0:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/error.log --open-files-limit=65535 --pid-file=node.pid --socket=/tmp/mysql.sock --port=3306 12 root 3606 0.0 0.0 112644 952 pts/1 S+ 02:13 0:00 grep --color=auto mysql 13 [root@node bin]# netstat -lnupt|grep mysql 14 tcp6 0 0 :::3306 :::* LISTEN 3583/mysqld 15 [root@node bin]#
MySQL5.6默认情况下启动后可以用空密码登录,直接修改密码:
1 [root@node bin]# ./mysql 2 Welcome to the MySQL monitor. Commands end with ; or \g. 3 Your MySQL connection id is 1 4 Server version: 5.6.40-log MySQL Community Server (GPL) 5 6 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 7 8 Oracle is a registered trademark of Oracle Corporation and/or its 9 affiliates. Other names may be trademarks of their respective 10 owners. 11 12 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 13 14 mysql> use mysql; 15 Reading table information for completion of table and column names 16 You can turn off this feature to get a quicker startup with -A 17 18 Database changed 19 mysql> update user set password=password('root@123') where user='root'; 20 Query OK, 4 rows affected (0.01 sec) 21 Rows matched: 4 Changed: 4 Warnings: 0 22 23 mysql> flush privileges; 24 Query OK, 0 rows affected (0.00 sec) 25 26 mysql> exit 27 Bye 28 [root@node bin]# ./mysql -uroot -hlocalhost -p 29 Enter password: 30 Welcome to the MySQL monitor. Commands end with ; or \g. 31 Your MySQL connection id is 2 32 Server version: 5.6.40-log MySQL Community Server (GPL) 33 34 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 35 36 Oracle is a registered trademark of Oracle Corporation and/or its 37 affiliates. Other names may be trademarks of their respective 38 owners. 39 40 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 41 42 mysql> show databases; 43 +--------------------+ 44 | Database | 45 +--------------------+ 46 | information_schema | 47 | mysql | 48 | performance_schema | 49 | test | 50 +--------------------+ 51 4 rows in set (0.01 sec) 52 53 mysql>
至此,MySQL5.6数据库二进制安装完成。
如果正常关闭数据库用如下命令:
1 [root@node bin]# ./mysqladmin -uroot -hlocalhost -p shutdown 2 Enter password: 3 [root@node bin]# 4 [root@node bin]# ps xua|grep mysql 5 root 3620 0.0 0.0 112644 948 pts/1 S+ 02:18 0:00 grep --color=auto mysql 6 [root@node bin]# netstat -lnupt|grep 3306 7 [root@node bin]#
一般便于操作,可以将MySQL的执行文件所在路径添加到系统环境变量PATH中:
1 [root@node bin]# pwd 2 /usr/local/mysql/bin 3 [root@node bin]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >>/etc/profile 4 [root@node bin]# source /etc/profile 5 [root@node bin]# cd 6 [root@node ~]# mysql 7 mysql mysql_config mysql_embedded mysqlslap 8 mysqlaccess mysql_config_editor mysql_find_rows mysqltest 9 mysqlaccess.conf mysql_convert_table_format mysql_fix_extensions mysqltest_embedded 10 mysqladmin mysqld mysqlhotcopy mysql_tzinfo_to_sql 11 mysqlbinlog mysqld-debug mysqlimport mysql_upgrade 12 mysqlbug mysqld_multi mysql_plugin mysql_waitpid 13 mysqlcheck mysqld_safe mysql_secure_installation mysql_zap 14 mysql_client_test mysqldump mysql_setpermission 15 mysql_client_test_embedded mysqldumpslow mysqlshow 16 [root@node ~]# mysql
这样我们可以在任何路径下直接执行MySQL相关的密令(即:/usr/local/mysql/bin目录下的可执行文件)