mysql的学习

--看看mysql中的进程情况

ps -ef | grep mysql

--登录mysql

mysql

--基本操作

show database;

create database test2;

user test2;

create table t (id int,name varchar(20));

desc t;

insert into t values(1,'bbk');

select * from t;

delete from t;

select * from t;

--mysql下载地址

http://www.mysql.com/downloads/mirror.php?id=404609

hit-2075@126.com

123456

--校验文件是否完整

md5sum ./mysql-5.1.51.tar.gz

tar -zxvf ./mysql-5.1.51.tar.gz

--查看源代码结构

cd mysql-5.1.51

--多平台版本文件夹

cd BUILD

vi check-cpu

cd ..

mysql_safe --user=mysql &

mysql

exit

--客户端源代码

cd client

ls

vi ./mysql.cc

--显示行号

:set nu

--查找主函数

/int main

:x

cd ..

--文档目录

cd Docs

vi ./mysql.info

:set nu

cd ..

--sql目录

cd sql

vi ./mysqld.cc

--------------------------------------------------mysql 源码编译

groupadd mysql

useradd -g mysql mysql

cd mysql-5.1.60

./configure --prefix=/opt/mysql5160/ \

 --without-debug \

 --enable-thread-safe-client \

 --enable-assembler \

 --enable-profiling \

 --with-mysqld-ldflags=-all-static \

 --with-client-ldflags=-all-static \

 --with-charset=latin1 \

 --with-extra-charsets=utf8,gbk \

 --with-mysqld-user=mysql \

 --without-embedded-server \

 --with-server-suffix=bbk \

 --with-plugins=innobase,partition

make

make install

cp support-file/my-medium.cnf /etc/my.cnf

cd /usr/local/mysql

bin/my_install_db --user=mysql

chown -R root

chown -R mysql var

chgrp -R mysql

------------------------------------------mysql的三种启动方式

--mysqld启动

cd libexec/

ll

--启动命令

./mysqld --defaults-file=../my.cnf --user=mysql

--再开一个窗口

--查看是否启动成功

ps -ef | grep mysql

--登录客户端

mysql

--mysql的安全启动，需要看的安全组

【mysqld】

【server】

【mysqld_safe】

--关闭

mysqladmin showdown

--msql安全启动

cd bin

vi ./mysqld_safe

cd ..

./bin/mysqld_safe --defaults-file=./my.cnf --user=mysql

--另一个窗口

--

ps -ef |grep mysql

mysql

exit

--用sockect登录

mysql -S /tmp/mysql.sock

mysqladmin shutdown

--后台执行

./bin/mysqld_safe --defaults-file=./my.cnf --user=mysql &

--mysql.server启动

cd share/mysql

ll | grep server

vi ./mysql.server

/start

--用mysql.server启动

./mysql.server --help

./mysql.server start

ps -ef | grep mysql

mysql

exit

mysqladmin shutdown

--封装成系统服务的形式

--拷贝到启动目录下

cp ./mysql.server /etc/rc.d/init.d/mysql

--配置启动列表

chkconfig --add mysql

chkconfig --list | grep mysql

service mysql start

ps -ef | grep mysql

mysql

exit

mysqladmin shutdown

cd ./libexec

--查看帮助

./mysqld --help --verbose | less

cd ..

ll

./bin/mysqld_safe --help | more

-----------------------------多实例启动

--查看

ps -ef  | grep mysql

./bin/mysqld_safe --defaults-file=./my.cnf --user=mysql &

pe -ef | grep mysql

kill -9 3855(父进程id)

--如果进程被杀，会自动启动

ps -ef | grep mysql

--mysqld_multi

mysqladmin shutdown

ps -ef | grep mysql

cd data

ll

cd ..

--创建另外两个实例的数据存放地

mkdir data2

mkdir data3

mysql_install_db --help

--创建实例

mysql_install_db --basedir=/opt/mysql5160 --datadir=/opt/mysql5160/data2 --user=mysql

ll

--查看创建实例后的变化

cd data2

cd ..

ll

--更改目录的归属

chown mysql.mysql ./data2 -R

chown mysql.mysql ./data3 -R

--创建第二个实例

mysql_install_db --basedir=/opt/mysql5160 --datadir=/opt/mysql5160/data3 --user=mysql

cd data3

cd mysql

ll

cd ../..

--备份配置文件

cp ./my.cnf ./my.cnf.bak

vi ./my.cnf

--新增

--增加多实例启动的命令路径

[mysqld_multi]

mysqld = /opt/mysql5160/bin/mysqld_safe

mysqladmin = /opt/mysql5160/bin/mysqladmin

user = test

password = test

--mysqld+端口号

--区别后面的多个实例

[mysqld3306]

port =3306

socket = /tmp/mysql3306.sock

pid-file =/tmp/mysql3306.pid

data

[mysqld3307]

port =3307

socket = /tmp/mysql3307.sock

pid-file =/tmp/mysql3307.pid

data2

[mysqld3308]

port =3308

socket = /tmp/mysql3308.sock

pid-file =/tmp/mysql3308.pid

data3

--启动实例

./bin/mysqld_multi --defaults-file=./my.cnf start 3306

ps -ef | grep mysql

./bin/mysqld_multi --defaults-file=./my.cnf start 3307

ps -ef | grep 3307

./bin/mysqld_multi --defaults-file=./my.cnf start 3308

cd /tmp

--登录实例

mysql -S ./mysql3306.sock

exit

mysql -S ./mysql3307.sock

exit

mysql -S ./mysql3308.sock

exit

cd /opt/mysql5160

vi ./my.cnf

./bin/mysqld_multi --defaults-file=./my.cnf stop 3308

cd share/

ll

vi ./mysqld_multi.log

mysql -S ./mysql306.sock

--展示语法

show grants for root@localhost;

--配置实例帐号

grant shutdown on *.* to 'test'@'localhost' identified by 'test' with grant option;

show grant for test@localhost;

exit

--查看历史命令

history | grep muti

--关闭实例

./bin/mysqld_multi --defaults-file=./my.cnf stop 3306

cd /opt/mysql5160

ps -ef | grep mysql

cd ./share/mysqld_multi.log

cd ..

ll

vi ./my.cnf

cd /tmp

ll

mysql -S ./mysql3306.sock

show grants for test@localhst;

history

./bin/mysqld_multi --defaults-file=./my.cnf stop 3306

ps -ef | grep 3306

cd /tmp

ll

mysql -S ./mysql3307.sock

grant shutdown on *.* to 'test'@'localhost' identified by 'test' with grant option;

exit

mysql -S ./mysql3308.sock

grant shutdown on *.* to 'test'@'localhost' identified by 'test' with grant option;

exit

mysqld_multi --defaults-file=./my.cnf stop 3307

ps -ef | grep 3307

mysqld_multi --defaults-file=./my.cnf stop 3308

ps -ef | grep 3308

history

--多实例启动

mysqld_multi --defaults-file=./my.cnf start 3306-3308

----------------------------------------------------------mysql的网络协议和常用命令

pwd

mysqladmin ping

ps -ef | grep mysql

mysql

exit

mysql --help | less

cd /tmp

mysql -S ./mysql.sock --protocol=ttt

mysql -S ./mysql.sock --protocol=socket

exit

mysql -h localhost --protocol=tcp

mysqladmin shutdown

mysqladmin ping

mysqladmin --help |less

mysqlimport --help |less

mysqldump --help |less

mysql -uroot -p -h localhost -P 3306

-------------------------------------------------------帮助文档

ps -ef | grep mysql

mysql

help;

--显示帮助专题

help contents;

--显示细节

help administration;

--显示细节

help index;

help show create view;

help contents;

? contents;

? data types;

? char;

? data contents;

? utility;

agdsg

--擦除命令

\c

--连接实例

connect mysql localhost;

select 1;

--替换执行符

delimiter $$

select 1;

delimiter ;

--调用vi

edit

select * from ...

--按页显示，显示方式为less

pager less

select * from information_schema.tables;

--按页显示，显示方式为more

pager more;

nopager;

--输出日志到磁盘

tee /tmp/rst.txt

show databases;

user information_shema;

show tables;

vi /tmp/rst.txt

--关闭输出

notee;

--提示符

prompt Mysql>;

--使用系统命令

system ls;

system cat /tmp/rst.txt;

source /tmp/a.sql;

--查看当前状态

status;

select database();

user mysql;

select database();

--设置字符集

charset gbk;

charset latin1;

show warnings

show errors;

cd /opt/mysql5160

vi ./my.cnf

--tab显示提示

#no-rehash

auto-rehash

user test;

show tables;

user information_schema;

user test;

show tables;

select t

select t.id from t;

? show;

--查看创建database语句

show create database test;

show create database test \G;

show tables;

--列行交换

show create t \G;

--显示变量

show variables;

show variables like '%buffer_cache%';

? set'

set global key_buffer_size=10000;

-------------------------------错误日志

./bin/mysqld_safe --user=mysql &

--错误日志的位置

show variables like '%error%';

--以系统名命名

system hostname;

mysqladmin shutdown

--指定错误日志的位置

./bin/mysqld_safe --user=mysql --log_error=/tmp/mysql3306.err

cd /tmp

ll

less mysql3306.err

----------------------------二进制日志

--二进制日志的启动

./bin/mysqld_safe --user=mysql --log-bin=/tmp/1.0000000001 --log-bin-index=/tmp/logbin.index --max-binlog-size=10M --binlog-do-db=test &

cat ./logbin.index

--查看日志参数

show variables like '%logbin%';

--二进制日志不能修改

set log_bin=1;

--显示二进制日志

show binary logs;

--二进制文件列表

cat ./logbin.index

--最大限制

show variables like '%max_binlog%';

select @@max_binlog_size/1024/1024;

--查看文件列表

show master logs;

show binary logs;

--切换日志

flush logs;

show binary logs;

flush logs

show binary logs;

--清除日志

reset master;

? purge;

flush logs

flush logs

flush logs

--清除日志到。。。

purge binary logs to '1.000003';

show binary logs;

--过期时间5天

show variables like '%expire%';

set global expire_logs_days = 5;

--重置

reset master

--查看二进制日志文件

mysqlbinlog ./1.000001

use test;

show tables;

create table t(id int);

show global variables like '%format%';

--修改格式为row，ddl日志就是机器码了

set global binlog_format='row';

set session binlog_format='row';

reset master;

select database();

insert into t values(1);

--dcl日志还是明文

show grants for root@localhost;

grant all privileges on *.* to user1@localhost;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15681893/viewspace-712277/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15681893/viewspace-712277/