1、环境介绍
系统:centos6.5
软件:mysql6.5(从官网下载版本时选择Linux-Generic、X86-64bit)
2、安装前系统检查
2.1、SElinux和防火墙是否关闭
2.2、操作系统的限制
修改系统的软硬限制,防止服务器的连接过多或表过多而出现打不开表或访问不了表的现象(“too many open files”)和服务器跑多个实例时报(“resource temporarily unavailable”)的错误,表示没有足够资源。编辑/etc/security/limits.conf, 注意:修改完成候重启系统才能生效
3、mysql数据安装过程
3.1、创建mysql用户,用户组,解压安装包,给安装包授权
1 #创建用户及用户组
2 groupadd mysql
3 useradd -g mysql mysql -s /sbin/nologin
4
5 #解压安装包至“/usr/local”下
6 tar -zxvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz -C /usr/local
7
8 #做软连接,方便日后升级
9 ln -s /usr/local/mysql-5.6.40-linux-glibc2.12-x86_64 /usr/local/mysql
10
11 #给mysql目录授权
12 chown mysql.mysql -R /usr/local/mysql
3.2、创建数据库的数据目录并授权
1 #创建数据库的数据目录
2 mkdir -p /data/mysql
3
4 #授权
5 chown -R mysql.mysql /data/mysql
3.3、配置数据库配置文件
vi /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
open_files_limit = 65535
back_log = 103
max_connections = 512
max_connect_errors = 100000
table_open_cache = 512
external-locking = FALSE
max_allowed_packet = 128M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 51
query_cache_size = 32M
tmp_table_size = 96M
max_heap_table_size = 96M
slow_query_log = 1
slow_query_log_file = /data/mysql/slow.log
log-error = /data/mysql/error.log
long_query_time = 0.5
server-id = 1323306
log-bin = /data/mysql/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 128M
max_binlog_size = 1024M
expire_logs_days= 7
key_buffer_size = 32M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
character-set-server=utf8
default-storage-engine=InnoDB
binlog_format=row
interactive_timeout=300
wait_timeout=300
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 1434M
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_locks_unsafe_for_binlog = 0
[mysqldump]
quick
max_allowed_packet = 32M
View Code
3.4、初始化数据库,一般情况下初始过程中出现两个OK,证明初始化数据库成功。
1 cd /usr/local/mysql/scripts
2 ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql --defaults-file=/etc/my.cnf --user=mysql
3.4、初始化数据库完成后,启动数据库:
1 cd /usr/local/mysql/bin
2 ./mysqld_safe --defaults-file=/etc/my.cnf &
查看mysql进程,验证是否启动成功
3.5、创建数据库密码
1 #mysql安装完成后,进入数据库的方式是无密码状态,为保证数据库安全,需给数据库root用户创建密码
2 mysql>use mysql;
3 mysql>update user set password=password('root123') where user='root';
4 mysql>flush privileges;
5
6 #安全加固,只保留数据库中用户为root,host为localhost的账号。
7 mysql>delete from user where user != 'root' or host != 'localhost';
8 mysql>flush privileges;
4、关闭mysql数据库:
1 #正常关闭
2 shell>cd /usr/local/mysql/bin
3 shell>./mysqladmin -uroot -proot123 shutdown
4
5 #非正常关闭
6 shell>ps -ef|grep mysql
7 #查看mysql数据库的进程ID,直接kill
至此,mysql数据库安装,启动和关闭完成
5、额外学习
1 #进入mysql数据库,通过show databases来查看当前数据库有哪些
2 mysql> show databases;
3 +--------------------+
4 | Database |
5 +--------------------+
6 | information_schema |
7 | mysql |
8 | performance_schema |
9 | test |
10 +--------------------+
11 4 rows in set (0.02 sec)
12
13 #我们主要了解一下information_schema和mysql库
14
15 #information_schema在安装mysql过程中的初始化阶段自动生成,它提供了访问数据库中元数据的所有信息,里面存放着关于mysql服务器所有其它数据库的信 息,如:数据库名、数据库里面的表、表数据类型和访问权限等,但该库是只读库,只能进行select操作,我们在information_schema中使用较多的表有:
16 * tables (记录所有表的基本信息,访问该表可收集表的统计信息)
17 * PROCESSLIST (查看当前数据库的连接)
18 * GLOBAL_STATUS (查看数据库运行的各种状态)
19 * GLOBAL_VARIABLES (查看数据库中的参数)
20 * PARTITIONS (数据库中表分区的情况)
21 * INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS 这三张表用来监控数据库中的锁情况
22
23 #mysql库也是初始化过程中自动创建的,我们主要使用user表来管理数据库中的用户权限信息
6、补充:mysql5.7安装与5.6的差异
6.1、mysql5.7安装与之前版本唯一的差异是初始化数据库哪一步,详细介绍如下:
1 #添加组
2 groupadd mysql
3 #添加用户
4 useradd -g mysql mysql -s /sbin/nologin
5 #解压安装包
6 tar -zxvf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local
7 #创建软连接
8 ln -s mysql-5.7.22-linux-glibc2.12-x86_64 mysql
9 #创建数据库目录
10 mkdir -p /data/mysql
11 #创建my.cnf
12
13 vi /etc/my.cnf
14
15 [client]
16 port = 3306
17 socket = /tmp/mysql.sock
18 [mysql]
19 prompt="\u@db \R:\m:\s [\d]> "
20 no-auto-rehash
21 [mysqld]
22 user = mysql
23 port = 3306
24 basedir = /usr/local/mysql
25 datadir = /data/mysql
26 socket = /tmp/mysql.sock
27 character-set-server = utf8mb4
28 skip_name_resolve = 1
29 open_files_limit = 65535
30 back_log = 1024
31 max_connections = 512
32 max_connect_errors = 1000000
33 table_open_cache = 1024
34 table_definition_cache = 1024
35 table_open_cache_instances = 64
36 thread_stack = 512k
37 external-locking = FALSE
38 max_allowed_packet = 32M
39 sort_buffer_size = 4M #用于sql语句在内存中的临时排序
40 join_buffer_size = 4M #表连接使用,用于BKA。
41 thread_cache_size = 768
42 query_cache_size = 0 #query_cache 高速查询缓存,在生产环境中建议关闭,‘0’为缓存数据大小为0,
43 query_cache_type = 0 #同上,和query_cache_size都是数据缓存的两个核心参数,都要关闭。‘0’为关闭,‘1’为开启(5.6以前的版本默认是关闭的)
44 interactive_timeout = 600
45 wait_timeout = 600
46 tmp_table_size = 32M #sql语句在排序或分组是没有用到索引,就会使用临时表空间
47 max_heap_table_size = 32M #管理heap,memory存储引擎表 (建议将tmp_table_size和该参数设置成一样的值,如果不一样,按照最小的值来限制)
48 slow_query_log = 1
49 slow_query_log_file = /data/mysql/slow.log
50 log-error = /data/mysql/error.log
51 long_query_time = 0.5
52 server-id = 3306128
53 log-bin = /data/mysql/mysql-binlog
54 sync_binlog = 1
55 binlog_cache_size = 4M
56 max_binlog_cache_size = 1G
57 max_binlog_size = 1G
58 expire_logs_days = 7
59 master_info_repository = TABLE
60 relay_log_info_repository = TABLE
61 gtid_mode = on
62 enforce_gtid_consistency = 1
63 log_slave_updates
64 binlog_format = row
65 relay_log_recovery = 1
66 relay_log_purge = 1
67 key_buffer_size = 32M #用于MyISAM存储引擎表,缓存MyISAM存储
68 read_buffer_size = 8M #表顺序扫描的缓存,只能应用于MyISAM表存储引擎
69 read_rnd_buffer_size = 4M #mysql随机读缓存区大小,用于做mrr
70 bulk_insert_buffer_size = 64M
71
72 lock_wait_timeout = 3600
73 explicit_defaults_for_timestamp = 1
74 innodb_thread_concurrency = 0
75 innodb_sync_spin_loops = 100
76 innodb_spin_wait_delay = 30
77 transaction_isolation = REPEATABLE-READ
78 innodb_buffer_pool_size = 1024M #innodb_buffer_pool用来缓存Innodb表的数据、索引、插入缓存、数据字典等信息
79 innodb_buffer_pool_instances = 8
80 innodb_buffer_pool_load_at_startup = 1
81 innodb_buffer_pool_dump_at_shutdown = 1
82 innodb_data_file_path = ibdata1:1G:autoextend #系统表空间参数
83 innodb_flush_log_at_trx_commit = 1
84 innodb_log_buffer_size = 32M #innodb_log_buffer用于事务在内存中的缓存,即:redo log buffer的大小
85 innodb_log_file_size = 2G
86 innodb_log_files_in_group = 2
87 innodb_io_capacity = 2000
88 innodb_io_capacity_max = 4000
89 innodb_flush_neighbors = 0
90 innodb_write_io_threads = 8
91 innodb_read_io_threads = 8
92 innodb_purge_threads = 4
93 innodb_page_cleaners = 4
94 innodb_open_files = 65535
95 innodb_max_dirty_pages_pct = 50
96 innodb_flush_method = O_DIRECT
97 innodb_lru_scan_depth = 4000
98 innodb_checksum_algorithm = crc32
99 innodb_lock_wait_timeout = 10
100 innodb_rollback_on_timeout = 1
101 innodb_print_all_deadlocks = 1
102 innodb_file_per_table = 1 #独立表空间 该参数设置为‘1’即可
103 innodb_online_alter_log_max_size = 4G
104 internal_tmp_disk_storage_engine = InnoDB
105 innodb_stats_on_metadata = 0
106 innodb_status_file = 1
107 innodb_status_output = 0
108 innodb_status_output_locks = 0
109 #performance_schema
110 performance_schema = 1
111 performance_schema_instrument = '%=on'
112 #innodb monitor
113 innodb_monitor_enable="module_innodb"
114 innodb_monitor_enable="module_server"
115 innodb_monitor_enable="module_dml"
116 innodb_monitor_enable="module_ddl"
117 innodb_monitor_enable="module_trx"
118 innodb_monitor_enable="module_os"
119 innodb_monitor_enable="module_purge"
120 innodb_monitor_enable="module_log"
121 innodb_monitor_enable="module_lock"
122 innodb_monitor_enable="module_buffer"
123 innodb_monitor_enable="module_index"
124 innodb_monitor_enable="module_ibuf_system"
125 innodb_monitor_enable="module_buffer_page"
126 innodb_monitor_enable="module_adaptive_hash"
127 [mysqldump]
128 quick
129 max_allowed_packet = 32M
130
131 #初始化数据库
132 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql --initialize #初始化数据库时加上--initialize参数会生成一个临时的数据库初始化密码,记录在log-error(错误日志)中,如果加上--initialize-insecure参数,表示无密码进入,建议使用生成初始密码
133
134 #启动数据库
135 /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
136 #修改目录属主
137 chown -R mysql.mysql /usr/local/mysql
138 chown -R mysql.mysql /data/mysql
6.2、启动完成后,修改初始密码,设置永不过期
#使用初始密码登录到数据库中
/usr/local/mysql/bin/mysql -uroot -pqpg47Wtts1#K
#设置密码
mysql>set password = 'password';
mysql>alter user 'root'@'localhost' password expire never;
mysql>flush privileges;