linux下mysql安装和调优

1.yum

yum -y install mysql-server mysql

2.RPM安装

http://dev.mysql.com/downloads/ 下载RPM包,请确认服务器版本,我的是红帽5.9,下载的是下面的版本,bundle是集合包

MySQL-5.6.21-1.rhel5.x86_64.rpm-bundle.tar

[root@localhost ~]# cd /soft/
[root@localhost soft]# ll
总计
-rwxr--r-- root root - : MySQL-5.6.-.rhel5.x86_64.rpm-bundle.tar
-rw-r--r-- wheel - : MySQL-client-5.6.-.rhel5.x86_64.rpm
-rw-r--r-- wheel - : MySQL-devel-5.6.-.rhel5.x86_64.rpm
-rw-r--r-- wheel - : MySQL-embedded-5.6.-.rhel5.x86_64.rpm
-rw-r--r-- wheel - : MySQL-server-5.6.-.rhel5.x86_64.rpm
-rw-r--r-- wheel - : MySQL-shared-5.6.-.rhel5.x86_64.rpm
-rw-r--r-- wheel - : MySQL-test-5.6.-.rhel5.x86_64.rpm

rpm -Uvh MySQL-*.rpm

mysql> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('root');
Query OK, rows affected (0.07 sec)

3.mysql的基本命令和字段类型信息

show databses;

use mysql;

show tables;

describe user;

create database cms;

select database();

linux下mysql安装和调优

DATETIME(占8字节)

TIMESTAMP(占4字节)

DATE(占4字节)

YEAR(占1字节)

TIME(占3字节)

mysql> create table user1 select * from te where username='xx';

mysql> create table user2 select * from te where false

mysql> alter table te rename to user;

mysql> alter table file change guid gid int(10) not null;

mysql> load data infile '/etc/passwd' into table file fields terminated by ":" lines terminated by "\n";

mysql> alter table file drop column id;

mysql> alter table file add id int(10) unsigned not null auto_increment primary key(id) first;

mysql> select * from file into outfile "/soft/user.txt" fields terminated by "\t" lines terminated by "\n";

service mysql start --skip-grant-table&update mysql.user set password=password("123") where user='root'

mysqladmin -uroot -p password "1234"

GRANT all ON db.* TO dbuser@'%' IDENTIFIED BY 'password'

REVOKE all ON db.* FROM dbuser@'%'

flush privileges;

MySQL日志类型

1.错误日志:记录数据库服务启动,运行过程中产生的错误信息 默认开启

2.查询日志:记录登录用户执行的sql语句 默认不开启 /etc/my.cnf

general-log

3.慢查询日志:超时查询sql 默认不开启,默认超时10s

slow-query-log

slow-query-log-file=iflytek-slow-query.log
long-query-time=5

4.binlog日志:使数据发生改变的sql语句 默认不开启

打开/etc/my.cnf

log-bin=mysql-bin【默认名称可以不写】

mysqlbinlog -start-position=4 -stop-position=200 bin-log.000001 | mysql -uroot -proot

show binlog events

flush logs;

purge master logs to "iflytek-bin.000002" 删除002之前的binlog

reset master 重新设置binlog日志

【etc/my.cnf】

log-bin=/mysqldata/iflytek-bin

chown mysql /mysqldata/

上一篇:Springboot(二):Spring Boot 之 HelloWorld


下一篇:Python-psutil模块