query_cache_size, tmp_table_size 这两个选项一定要设置!
 
# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port           = 3306
socket                = /home/work/mysql/tmp/mysql.sock
 
# Here follows entries for some specific programs
 
# The MySQL server
[mysqld]
port           = 3306
bind-address = 127.0.0.1
socket                = /home/work/mysql/tmp/mysql.sock
#skip-locking
max_allowed_packet = 1M
table_open_cache = 16
sort_buffer_size = 8M
read_buffer_size = 512K
read_rnd_buffer_size = 256K
net_buffer_length = 8K
thread_stack = 128K
 
# Size of the Key Buffer, used to cache index blocks for MyISAM
tables.
# Do not set it larger than 30% of your available memory, as some
memory
# is also required by the OS to cache rows. Even if you’re not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size = 8M
 
# If the temporary file used for fast index creation would be bigger
# than using the key cache by the amount specified here, then prefer
the
# key cache method.  This is mainly used to force long character keys
in
# large tables to use the slower key cache method to create the
index.
myisam_sort_buffer_size = 8M
 
# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the
query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# “Qcache_lowmem_prunes” status variable to check if the current
value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size=16M
 
# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be
many
# of them.
tmp_table_size=16M
 
# Don’t listen on a TCP/IP port at all. This can be a security
enhancement,
# if all processes that need to connect to mysqld run on the same
host.
# All interaction with mysqld must be made via Unix sockets or named
pipes.
# Note that using this option without enabling named pipes on Windows
# (using the “enable-named-pipe” option) will render mysqld useless!
#
skip-networking
server-id  = 1
 
# Uncomment the following if you want to log updates
#log-bin=mysql-bin
 
# binary logging format – mixed recommended
#binlog_format=mixed
 
# Uncomment the following if you are using InnoDB tables
#default_storage_engine=InnoDB
 
innodb_data_home_dir = /home/work/mysql/var/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/work/mysql/var/
# You can set .._buffer_pool_size up to 50 – 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 32M
innodb_additional_mem_pool_size = 4M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 10M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
 
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
 
[mysqlhotcopy]
interactive-timeout

MySQL安装与基本配置,MySQL安装基本配置

一、简介

SQL语言

DDL:表、视图、索引、触发器操作等。CREATE/ALTER/DROP语句

DML:数据操作。SELECT/INSERT/UPDATE/DELETE

DCL:权限设置。GRANT/REVOKE

 

数据库访问

不同的语言使用不同的数据库访问技术

C#使用ADO.NET,JAVA使用JDBC等

 

版本

Community Edition:免费、自由下载,无技术支持

Enterprise:收费、不能下载,有技术支持

Alpha:开发阶段

Beta:开发完成,未测试

Gamma:已发行一段时间的测试版

Generally Available(GA):稳定版

 

工具

MySQL Community Server:客户端和服务器整合起来的核心包

MySQL Cluster:提供Mysql集群功能的程序包

MySQL Fabric:为高可用性和分片管理提供了一个框架

MySQL Utilities:提供维护和管理的实用工具

MySQL Workbench:可视化编辑工具

MySQL Proxy:MySQL中间件,代理接收发往MySQL数据库的请求,将需要求路由至不同的后端主机上去

MySQL Connectors:MySQL的连接器,程序连接MySQL的驱动

MySQL Yum Repository:下载MySQL的YUM源

MySQL APT Repository:APT源

 

RPM包

MySQL-client:客户端连接工具,GUI工具有navicat、phpmyadmin等

MySQL-server:服务器包

MySQL-devel:库和包含文件

MySQL-shared:某些语言和应用程序需要动态装载的共享库

MySQL-test:测试组件

MySQL-embedded:嵌入式

MySQL-bundle:整合包

 

相关文件

MySQL Community Server:客户端和服务器整合起来的核心包

MySQL Cluster:提供Mysql集群功能的程序包

MySQL Fabric:为高可用性和分片管理提供了一个框架

MySQL Utilities:提供维护和管理的实用工具

MySQL Workbench:可视化编辑工具

MySQL Proxy:MySQL中间件,代理接收发往MySQL数据库的请求,将需要求路由至不同的后端主机上去

MySQL Connectors:MySQL的连接器,程序连接MySQL的驱动

MySQL Yum Repository:下载MySQL的YUM源

MySQL APT Repository:APT源

 

二、安装

环境:CentOS 6.7 x86、MySQL 5.6

 

报错,删除mysql-libs包

[[email protected] MySQL]# rpm -qa | grep -i mysql
mysql-libs-5.1.73-5.el6_6.i686
[[email protected] MySQL]# yum remove mysql-libs-5.1.73-5.el6_6.i686

 

RPM

[[email protected] MySQL]# rpm -ivh MySQL-server-5.6.26-1.el6.i686.rpm
[[email protected] MySQL]# rpm -ivh MySQL-client-5.6.26-1.el6.i686.rpm

 

YUM

[[email protected] ~]# yum install mysql-server mysql-client

 

防火墙

iptables -A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT service iptables save service iptables restart

 

配置

5.1版本root无密码;5.6安装完成后,root生成随机密码在/root/.mysql_secret

5.1有配置文件;5.6无配置文件,需从/usr/share/mysql/my-default.cnf复制到/etc/my.cnf

5.1服务名mysqld,5.6为mysql

 

开启服务

[[email protected] ~]# service mysql start

 

查看登录数据库账号root的密码

[[email protected] ~]# cat .mysql_secret 
# The random password set for the root user at Tue Sep  8 11:26:39 2015 (local time): 4VZTzey0LML2N7e1

 

初始化设置

[[email protected] ~]# /usr/bin/mysql_secure_installation --user=mysql

#输入root的密码
Enter current password for root (enter for none): 

#是否修改root的密码
Change the root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!

#删除匿名用户
Remove anonymous users? [Y/n] y
 ... Success!

#禁止root远程登录
Disallow root login remotely? [Y/n] y
 ... Success!

#删除测试数据库
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

#重新加载权限表
Reload privilege tables now? [Y/n] y
 ... Success!

 

三、连接数据库

连接

参数:

-h:主机名或IP

-P:端口号,默认3306

-u:用户名

-p:密码

-e:指定SQL语句

 

[[email protected] ~]# mysql -u root -p
Enter password: 
[[email protected] ~]# mysql -u root -p db01#连接db01库
[[email protected] ~]# mysql -h 192.168.41.135 -u root -p#远程连接

 

四、创建远程用户

user1用户拥有所有权限,%表示任意主机可登录

mysql> create user 'user1'@'%' identified by '123456';
mysql> grant all privileges on *.* to 'user1'@'%';

 

五、迁移data目录

data目录应独立分区

 

关闭服务

[[email protected] ~]# service mysql stop

 

移动目录

[[email protected] ~]# mv /var/lib/mysql/ /data/

 

配置文件

[[email protected] ~]# cp -a /usr/share/mysql/my-default.cnf /etc/my.cnf
[[email protected] ~]# vim /etc/my.cnf
datadir=/data/mysql
socket=/data/mysql/mysql.sock

[mysql]
socket=/data/mysql/mysql.sock

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 

启动服务

[[email protected] ~]# service mysql start

 

SELinux报错解决方法(mv命令保留权限,所以没报错)

[[email protected] ~]# chcon -R -t mysqld_db_t /data//mysql

 

本文出自 “运维菜鸟.log” 博客,谢绝转载!

一、简介
SQL语言 DDL:表、视图、索引、触发器操作等。CREATE/ALTER/DROP语句
DML:数据操作。SELECT/I…

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图