mysql学习笔记

最新推荐文章于 2024-04-24 16:25:27 发布

cuixun1493

最新推荐文章于 2024-04-24 16:25:27 发布

阅读量78

点赞数

./mysqld --defaults-file=../my.cnf --user=mysql
mysql -S /tmp/mysql.sock
start:
cd share/mysql
./mysql.server start (./mysql.server --help)
cp ./mysql.server /etc/rc.d/init.d/mysql
chkconfig --add mysql
chkconfig --list|grep mysql
service mysql start
libexec]#./mysqld --help --verbose | less
读配置文件顺序：
/etc/my.cnf /etc/mysql/my.cnf /opt/mysql5152/etc/my.cnf ~/.my.cnf
./bin/mysqld_safe --help
show binary logs
system cat ls -l /tmp
help;
help contents;
help administration
mysql_install_db --basedir=/opt/mysql5152 --datadir=/opt/mysql5152/data2 --user=mysql
在data2下面生成两个库
mysql_install_db --basedir=/opt/mysql5152 --datadir=/opt/mysql5152/data3 --user=mysql
mysql_multi --defaults-file=./my.cnf start 3306(3307 3308)或者3306-3308
show grants for root@localhost;

mysqladmin ping
mysqld is alive
status or \s
charset gbk
? show
show create database test; 或者show create database test \G;
show create table t [\G]
show variables[like '%buffer%];
set global key_buffer_size=10000;
select @@max_binlog_size/1024/1024
error log可以通过指定 --log-error[=file_name] log_error在my.cnf里面指定(暂不生效) 默认目录在data里面的host.err
[root@Linux mysql5]# ./bin/mysqld_safe --user=mysql --log-error=/tmp/mysql5.err &
mysql> show variables like '%error%';
+--------------------+----------------------------------+
| Variable_name      | Value                            |
+--------------------+----------------------------------+
| error_count        | 0                                |
| log_error          | /usr/local/mysql5/data/Linux.err |
| max_connect_errors | 10                               |
| max_error_count    | 64                               |
| slave_skip_errors | OFF                              |
+--------------------+----------------------------------+
binary log
命令行参数：
--log-bin[=file_nam]二进制日志生成的位置
--log-bin-index[=file]记录了二进制日志的所有名字
--max_binlog_size 二进制日志单个日志文件的大小
--binlog-do-db=db_name对哪些数据库进行日志记录,注意如果启动加这个选项，就说明只对这些日志记录，其它忽略
--binlog-ignore-db=db_name和上面相反，对哪些数据库不进行日志记录，也就是对指定数据库不进行日志记录，其它都记录
系统变量
log_bin
binlog_cache_size
max_binlog_cache_size
max_binlog_size
binlog_cache_use
binlog_cache_disk_use
binlog_do_db
binlog_ignore_db
sync_binlog
./bin/mysqld_safe --user=mysql --log-bin=/usr/local/mysql5/log-bin/1.0000000001 --log-bin-index=/usr/local/mysql5/log-bin/logbin.index --max-binlog-size=10M --binlog-do-db=test &
或者在my.cnf里面配置log_bin = /usr/local/mysql5/log-bin/1.000001，重启服务可以生效

mysql> show binary logs;
+----------+-----------+
| Log_name | File_size |
+----------+-----------+
| 1.000001 | 106 |
+----------+-----------+
1 row in set (0.01 sec)

show master logs = show binary logs
flush logs日志切换
mysql> show master logs;
+----------+-----------+
| Log_name | File_size |
+----------+-----------+
| 1.000001 | 106 |
+----------+-----------+
1 row in set (0.00 sec)

mysql> flush logs; 日志切换，会又生成一个日志，递增
Query OK, 0 rows affected (0.03 sec)

mysql> show binary logs;
+----------+-----------+
| Log_name | File_size |
+----------+-----------+
| 1.000001 | 141 |
| 1.000002 | 106 |
+----------+-----------+
2 rows in set (0.01 sec)

reset master;初始化日志删除掉后再重新生成
mysql> show binary logs;
+----------+-----------+
| Log_name | File_size |
+----------+-----------+
| 1.000001 |       141 |
| 1.000002 |       141 |
| 1.000003 |       106 |
+----------+-----------+
3 rows in set (0.00 sec)

mysql> reset master; 删除掉所有日志，重新生成一个，即初始化
Query OK, 0 rows affected (0.03 sec)

mysql> show binary logs;
+----------+-----------+
| Log_name | File_size |
+----------+-----------+
| 1.000001 | 106 |
+----------+-----------+
1 row in set (0.00 sec)
mysql> ? purge
purge binary logs to '1.000003'; 03前的全部删掉了
show variables like '%expire%';
set global expire_log_days=5 指定日志失效期
mysqlbilog 00001.bin|grep insert
set global binlog_format=row;
show global variables like '%format%';
binlog_format:statement,row,mixed
set session binlog_format='STATEMENT'('ROW';'MIXED')
set global binlog_format='STATEMENT';
binlog_cache_size每个线程，而不是整个数据库
max_binlog_cache_size最大扩展
show status like '%binlog_cache_use%'; status可变的
show table status like 't' \G;
MYISAM不支持事物
select @@autocommit;
set autocommit=0 0关闭1打开
show variavles like '%binlog_cache_use%'; 看缓存了多少个事物
binlog_cache_disk_use 内存不够，磁盘上存储
sync_binlog
show create procedure test \G;
call test();调用
general_log=1
genaral_log_file=/tmp/mysqlgen.log
mysqladmin flush-logs重新生成
slow_query_log=1
slow_query_log_file=/tmp/mysqlslow.log1
show index from t;看t有没有索引
mysqldumpslow -s -t -t 2 /tmp/mysqlslow.log1把慢查询按时间最长的前两条查出来
.frm表结构文件

mysql> set global max_connections=2;
mysql> set global max_user_connections=2;

mysql> show global status like '%connect%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Aborted_connects     | 1     |
| Connections          | 3     |
| Max_used_connections | 2     |
| Threads_connected    | 1     |
+----------------------+-------+

desc tables_priv \G （垂直显示列）
mysql> show create table t \G
mysql> show table status like 't'\G
开始事物：set autocommit=0 只是session级别的，
          start transaction; commit;
mysql> insert into t(msg) values('d'),('f');
Query OK, 2 rows affected (0.01 sec)
mysql> select last_insert_id();
mysql> alter table t auto_increment=100;
mysql> create table t (id int) data directory='/tmp/data/DATA' index directory='/tmp/data/INDEX';
mysql> show variables like '%autocommit%';
mysql> set session autocommit=off;
mysql> show session variables like '%autocommit%';
mysql> check table t;
+--------+-------+----------+----------------------------------------------------------+
| Table | Op    | Msg_type | Msg_text                                                 |
+--------+-------+----------+----------------------------------------------------------+
| test.t | check | error    | Size of datafile is: 8388669         Should be: 58720263 |
| test.t | check | error    | Corrupt                                                  |
+--------+-------+----------+----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> lock table t write;其他session连查询都不行，但是mysql> insert delayed into t values(4,'fuck');可以哦
mysql> unlock table t;

lock table t read;只锁定表的当前版本，别人可以查，但是不可以写
mysql> lock table t read local; 别人可以插入，但是不能看到别人插入的数据，自己不可以写。但是insert low_priority into t values(4,'guan');会挂住
mysql> select high_priority * from t;
mysql> insert low_priority into t values(2,'liang');
mysql> show variables like '%delayed%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| delayed_insert_limit       | 100   |
| delayed_insert_timeout     | 300   |
| delayed_queue_size         | 1000 |
| max_delayed_threads        | 20    |
| max_insert_delayed_threads | 20    |
+----------------------------+-------+
5 rows in set (0.01 sec)
delayed_insert_limit 每insert 100行记录前看有没有select操作，如果没有，插入100行，反复
delayed_insert_timeout 终止之前的等待时间
delayed_queue_size延迟插入超过多少行时候会产生等待
mysql> create table t (id int,msg char(10)) max_rows=5 min_rows=2;可以插入超过6行，收到下面参数控制
mysql> show global variables like '%point%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| myisam_data_pointer_size | 6     |
+--------------------------+-------+
mysql> set global myisam_data_pointer_size=2;
mysql> select 65536/1024;
+------------+
| 65536/1024 |
+------------+
|    64.0000 |
+------------+
1 row in set (0.00 sec) 65536=2的16次方，不能超过64K
load data infile自动disable和enable index更新，所以比insert快
mysql> select * from t into outfile '/tmp/liang.txt' fields terminated by ',' enclosed by '"';
[root@Linux tmp]# cat liang.txt
"1","my"
"2","liang"
"3","liangs"
"4","guan"
"4","qun"
"4","you"
"4","fou"
mysql> load data infile '/tmp/liang.txt' into table t fields terminated by ',' enclosed by '"';

./configure --prefix=/usr/local/mysql5 --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

--with-plugins=innobase不加这个不能支持innodb表
mysql> create table t (id int,msg char(10)) engine=merge union(t1,t2,t3) insert_method=last;
mysql> insert into t values(5,'e');由于创建时候insert_method=last，所以插入是t3表
mysql> set password for 'liang'@'localhost'=password('liang'); 也可以这样修改密码
mysql> show grants for 'tom'@'localhost';
mysql> grant all privileges on *.* to 'jack'@'localhost' with max_queries_per_hour 2;
密码忘记操作：
如忘记root密码，就关闭数据库
[root@Linux mysql5]# ./bin/mysqld_safe --skip-grant-tables &
mysql> update user set password='' where user='root' and host='localhost';
重启数据库后密码为空了。

备份
非事物表的一致备份：
mysqldump --skip-opt --quick --extended-insert=false --lock-all-tables --master-data=2 --databases test1 test2 >/tmp/tt.sql
事物表的一致备份
mysqldump --skip-opt --quick --extended-insert=false --single-transaction --master-data=2 --databases test1 test2 >/tmp/tt.sql

show binary logs看到两个日志
reset master;变成一个,把以前的日志给清除了
flush logs 日志切换
show binlog events in 'mysql-bin.000001';
grep "CHANGE MASTER" ./test1.sql找出备份的日志位置
mysql < ./test1.sql
mysqlbinlog --start-position=106 --stop-position=484 -vv ./mysql-bin.000001 可以查看
mysqlbinlog --start-position=106 --stop-position=484 -vv ./mysql-bin.000001 | mysql -u -p应用恢复日志

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

转载于:http://blog.itpub.net/25602767/viewspace-700041/

cuixun1493

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql学习笔记

./mysqld --defaults-file=../my.cnf --user=mysqlmysql -S /tmp/mysql.sockstart:cd share/mysql./mysql.server start (....
复制链接

扫一扫