来源：自学PHP网 时间：2015-04-16 10:50 作者： 阅读:次
[导读] 一，创建表T1：create table t1(col1 char(6) not null default ,col2 character(6),col3 varchar(6),col4 national varchar(6)); 二，查看表结构：desc t1;explain t1;show colum...
|
一，创建表T1： create table t1( col1 char(6) not null default '', col2 character(6), col3 varchar(6), col4 national varchar(6));
二，查看表结构：
show create table t1; CREATE TABLE `t1` ( `col1` char(6) NOT NULL DEFAULT '', `col2` char(6) DEFAULT NULL, `col3` varchar(6) DEFAULT NULL, `col4` varchar(6) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 从查看表结构show create table t1 可以看出，MySQL系统里把char和character当做是一样的，都用char表示；varchar和national varchar是一样的，都用varchar表示。 四，增加一个表列 alter table t1 add column col5 varchar(30);
五，删除一个表列 alter table t1 drop column col6;
六，查看警告 alter table t1 drop column col8; 表中没有col8的列 Level Code Message 'Error', '1091', 'Can''t DROP ''col8''; check that column/key exists'
七，sql_mode的查看和设置 查看sql_mode 的默认值 select @@sql_mode; 修改sql_mode的默认值 set sql_mode='pad_char_to_full_length'; 可以同时设置多个值，中间用逗号隔开 SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
八，查看帮助 help year; create table t1( col1 char(6) not null default '', col2 character(6), col3 varchar(6), col4 national varchar(6)); char和character是一样的 查看表结构：
alter table t1 drop col2; show warning; sql_mode 默认是'' select length(col1),col1,length(col3),col3 from t1; alter table t1 var_t add col2 varchar(1000); create table var_utf(col1 varchar(65000)) charset=utf8;
TINYBLOB，BLOB，MEDIUMBLOB，LONGBLOB FULLTEXT：全文索引 ENUM：枚举,返回的是索引,最大长度65535
SET can support 64 members
create table set_t(col1 set('F','M','UN'));
insert into set_t values('F');
insert into set_t values('M');
insert into set_t values('FM');
insert into set_t values('F,M');
insert into set_t values('F,M,UM');
select col1,col1+0 from set_t;
tinyint 1Byte
smallint 2Bytes
mediumint 3Bytes auto_increment
int(integer) 4Bytes auto_increment money,salary, int(1) 1表示显示的宽度
bigint 8Bytes science digit,population
create table int_t(id int(2));
insert into int_t values(12345678);
insert into int_t values(1234567899);
create table int_t2(id int(6) zerofill);
insert into int_t2 vlues(12345678);
insert into int_t2 vlues(123);
help decimal;
decimal 默认是10个bytes
decimal(g,f) g can up to 65, g can up to 30
create table dec_t(id decimal(10,2));
insert into desc_t values(1000.22);
DATETIME 8Bytes
YEAR
DATE 3Bytes
TIME 3Bytes
TIMESTAMP
Zero
mysql>help year;
create table y_t1(col1 year(2),col2 year(4),col1 year(100));
insert into y_t1 values('77','1977-01-09','');
insert into y_t1 values('','','2188');
create table timestamp_t1(col timestamp default current_timestamp on update current_timestamp,id smallint);
desc timestamp;
insert into timestamp_t1 values('',100);
insert into timestamp_t1 values(now(),200);
update timestamp_t1 set id=101 where id=100;
加载入时区到mysql数据库 cd /usr/local/mysql/bin .mysql_tzinfo_to_sql /usr/share/zoneinfo | /usr/local/mysql/bin/mysql - uroot -S /tmp/mysql3307.sock mysql select * from create table tz_t1(login_d timestamp default current_timestamp); show variables like 'time_zone%'; insert into tz_t1 values(now()); select * from tz_t1; set time_zone='America/Los_Angeles'; show character set; 查看当前数据库字符集 2014-07-08 5th as begining create table bit_t1(bt bit(10)); insert into bit_t1 values (0),(1),(2); select * from bit_t1; select bt,bt+0 from bit_t1; select bt,bt+0,bin(bt) from bit_t1; insert into bit_t1 set bt=b'11111'; select bt,bt+0,bin(bt) from bit_t1; select * from bit_t1 where bt=11111; select * from bit_t1 where bt='11111'; select * from bit_t1 where bin(bt)=11111; delete from bit_t1 where bin(bt)=11111; float(p) p: precision p最大24 如果大于24直接专成double create table flo_t(id float(10),id2 float(24), id3 float(25)); desc flo_t; alter table flo_t add id4 float(50)); desc flo_t; DOUBLE DOUBLE(g,f) show variables like 'sql_mode'; set sql_mode='real_as_float'; create table real_t(id real(10)); help real; create table real_t(id real(10,2)); desc real_t; set sql_mode=''; drop table real_t; create table real_t(id real(10,2)); NOT NULL NULL DEFAULT AUTO_INCREMENT UNSIGNED ZEROFILL CRATE table auto_t1(id smallint auto_increment primary key); desc auto_t1; insert into auto_t1 values(); select * from auto_t1; insert into auto_t1 values(); select * from auto_t1; show variables like 'auto_increment%'; select last_insert_id(); BOOL HELP BOOL; help boolean; select if(0,'true','fals'); MySQL management mysql mysqladmin mysqlshow which mysql vi .mysql_history mysql>status mysql>show databases; mysql>\s mysql>select database(); mysql>select current_user(); mysql>nopager mysql>create database ch charset=utf8; mysql>use ch; mysql>\s mysql>set names utf8; mysql>\s mysql>grant all on *.* to watson@localhost identified by 'watson'; flush privileges; 三种连接数据库方式： mysql -u watson -p mydbname mysql -u watson -p -D mydbname mysql -u watson -p --database=mydbname 切换数据库： mysql>use dbname; mysql>\u dbname vi pwd watson chmod 700 pwd mysql -uwatson -p'cat pwd' vi show set names utf8; show databases; show databases; use test; create table tt(id int not null,name varchar(100)) engine=innodb; 非交互方式： rename table tt to ttt; mysql>source show; pwd mysql>\. /root/show; #mysql -uwatson -p'cat pwd' -e "show databases;" #mysql -uwatson -p'cat pwd' -e "show processlist;" #mysql -uwatson -p'cat pwd' -e "show master status;" #mysql -uwatson -p'cat pwd' -e "show master status\G;" exit,quit or \q
mysqladmin命令：
shutdown database
create database
drop database
display variables & status
flush
kill
mysqladmin -uwatson -p'cat pwd' create dbname
mysqladmin -uwatson -p'cat pwd' drop dbname
mysqladmin -uwatson -p'cat pwd' extended-status
mysqladmin -uwatson -p'cat pwd' flush-privileges
mysqladmin -uwatson -p'cat pwd' processlist
mysqladmin -uwatson -p'cat pwd' kill processid
mysqladmin -uwatson -p'cat pwd' ping
/etc/init.d/mysqld stop
mysqladmin -uwatson -p'cat pwd' ping
/etc/init.d/mysqld start
mysqladmin -h 192.168.92.100 -uwatson -p'cat pwd' ping ping其他的主机
mysqladmin -uwatson -p'cat pwd' status --sleep=1 --count=2
mysqladmin -uwatson -p'cat pwd' status --sleep=1 --count=2
mysqlshow用法：
mysqlshow --help
mysqlshow
mysqlaccess --howto
mysqlaccess --help
Mysql query browser 像OEM
http://dev.mysql.com/downloads/gui-tools
Metadata元数据 data dictionary(frm file)
create table my1(id int) engine=myiasm;
file my1.frm
strings my1.frm
create table my2(id int) engine=innodb;
create table csv1(id int not null) engine=csv;
create table arc1(id int not null) engine=archive;
create view v1 as select * from my1;
show create table v1;
strings v1.frm
show variables 'version%'
set @a='abc';
select @a;
show variables like 'sort_buffer%';
set sort_buffer_size=256*1024;
set sort_buffer_size=default;
show variables like 'tx_isolation%';
help isolation;
set session transaction isolation level read committed;
show variables like 'tx_isolation%';
set global transaction isolation level read uncommitted;
use information_schema;
desc global_variables;
select * from global_variables where variable_name='max_connections';
show variables like 'max_connections%';
select @@global.max_connections
set global max_connections=1000;
show status;
show status like 'open%';
show global status ;
show session status ;
show global status like 'open%';
show session status like 'open%';
show master status;
show slave status;
desc tables;
select * from tables where table_type !='SYSTEM VIEW' limit 1\G;
show profiles;
show varibales like 'prof%';
set profiling = ON;
select * from tables where table_type !='SYSTEM VIEW' limit 1\G;
SHOW PROFILES;
help SHOW PROFILES;
show profile for query 1;
show profile CPU for query 1;
USER_privileges;
schema_privileges;
table_privileges;
column_privileges;
desc processlist;
Binary Log:
record of changes(ddl,dml)
Used for replication or Point-In-Time-Recovery(PITR)
how to enable binlog
vi /etc/mysql/my.cnf
#log_bin = /opt/binary/[basename]
log_bin = /opt/binary/CHANGE
mkdir /opt/binary/
chown -R mysql /opt/binary/
启动服务
/etc/init.d/mysql start
cd /opt/binary
ls -lt
flush logs; 回自动的切换日志文件
help purge;
自动删除log
--expire_log_days
show variables like 'expire_log%';
binlog files
Index file
cat CHANGE.index
mysqlbinlog CHANGE.000001
show binlog events;
- IN file
- FROM position
- LIMIT events
show binlog events
help show binary logs;
show binlog events in 'CHANGE.000002';
show binlog events in 'CHANGE.000002' from 106;
show binlog events in 'CHANGE.000002' from 106 limit 1;
purge binary logs to filename;
purge binary logs before datetime;
purge binary logs to 'CHANGE.000001';
purge binary logs to 'CHANGE.000002';
flush logs;
insert into a values (131,'gen');
show binary logs;
show binlog evnets in 'CHANGE.000003';
purge binary logs before '2010-03-31 7:25:09';
RESET MASTER 很危险，删除所有的logs ,生产环境注意一定。相当与oracle 的 resetlogs
Binary Log Structure:
common header
post-header
mysqlbinlog CHANGE.000004
Context event(s) + Query event = Binlog Group
select rand();
create table a(id int);
insert into a values( rand());
The replicaion user with the REPLICATION SLAVE privilege can read everything.
alter table a add pass varchar(200);
update a set pass=password('123');
如何让密码在log里加密看不到明文
set @passwd=password('123')
update a set pass=@passwd;
如何查看log文件
show variables like 'log%';
show variables like '%log%';
Error_log --content(text) :includes error occur|startup|shutdown
Specify:--log_error=file_name (option) log_error=file_name(configuration)
Rotate log: flush logs, (shell: mysqladmin flush_logs mysqladmin refresh)
启动日志： mysql_safe --user=mysql &
关闭日志：mysqladmin -S /tmp/mysql3308.sock shutdown
mysqld --verbose --hlep | grep debug
which mysqld (查看mysqld在什么路径)
mysqld --debug=d,info,error,query,genernal,where:o,/temp/mysqld.trace --user mysql &
show variables like 'max_connect%';
gdb -p 25056 -ex "set max_connections=200" -batch
show variables like 'max_connect%';
general_log(普通日志)
-all queries
-be used to debug
config:
--log[=file_name]
-Log_output=[]
mysql --log=/opt/mysql/data/gen.log --user=mysql &
show variables like 'log_output%';
mysql> set log_output='TABLE';
mysql.general_log表里
mysql>set global log_output=NONE 取消日志
slow_query_log
show variables like '%slow%';
mysql>set log_slow_queries=ON;
mysql>set slow_query_log=ON; 慢查询
慢查询的工具tools: mysqlslowdump maatkit mysqlsla
binary_log
Relay log (master slave下的)
innodb redo log
MYISAM：
Not support:
Transactions
FK
MVCC
Clustered indexes
Data Caches
Cluster Database Support
Physical structure:
.frm
.myd
.myi
$datadir/database/tb.?
show engines;
/etc/my.cnf
default_storage_engine=myiasm
.MYD
1,fixed
2,dynamic
3,packed
show columns from table
:%!xxd看16进制
Full-text search indexes用来解决like查询
alter table tb add index(col1);
alter table tb add unique index(col2);
show index from tb;
key_buffer_size =200M|2G|256*1024*1024 cache index block not data block 可以被所有thread共享
set global hotcache.key_buffer_size=60%
eg. set global hotcache.key_buffer_size=10*1024*1024
cache index a in hotcache;
set global warmcache.key_buffer_size=20%
set global coldcache.key_buffer_size=20%大小
上面的也可以防到/etc/my.cnf里
hotcache.key_buffer_size=10M
可以写个文件：
vi /etc/mysql/initcache.sql
cache index test.a in hotcache
然后把上面的文件防到/etc/my.cnf里
init_file=/etc/mysql/initcache.sql
然后重新启动就可以了
concurrent_insert=0，1，2
delay_key_write=ON，OFF，ALL enumeration 枚举类型的
max_write_lock_count =5
表示等5个写锁完成后，才可以读
low_priority_updates=OFF则表是读的级别高，写的级别低
preload_buffer_size default 32k 预载入多少buffer_size
show status like 'Key%';
myiasm_block_size default 1024Byte
查看linux的块的大小： getconf PAGESIZE
myiasm_data_pointer_size default value is 6
tmpdir
myiasm_recover
myiasm_recover_options=DEFAULT,BACKUP,FORCE,QUICK
myiasm_repair_threads
myisam_use_mmap mmap:memory mapping
Query Cache:
show status like 'qcache%';
show status like 'com_%';
show status like 'query_cache%';
query cache会保存sql statement 和 result set
清除query cache
flash query cache;
Lock(s) in mysql: MYIASM Engine
Internal locking--MySQL server
External locking: OS -File system
读写同时请求的时候，写优先，读等待。
写的时候会锁整个表。串行化写
read blocks write 读锁会阻塞写
mysql>promp session1
help lock
lock table t1 read 强制加锁
select * from t1;
如果当前session没有解锁，则他不能查询其他的信息了，但是其他的session是可以读的。
select * from mysql.user limit 1;
释放锁：退出session 或者unlock tables;
write blocks read 写阻塞读(mysql 5.1之前的版本，之后是不阻塞的)
lock table t2 write; 强制加写锁
unlock tables
write blocks write 写阻塞写(指的是其他的session是不可以写的，当前的session是可以做ＤＭＬ的)
concurrent insert & select (concurrent insert =0|1|2)
并发的插入
help lock;
Myisam Tools:
Mysqlcheck
Myisamchk
--checks,repairs,optimizes,or analyzes tables
mysqlcheck --help
mysqlcheck [options] databasename tablename,must be used when the mysqld server is running
创建一个内存表：
create table mem(id int) engine=memory;
show create table mem;
mysqlcheck test mem; 不支持内存表
mysqlcheck test t1 t2; 可以检查一系列表
mysqlcheck --databases test cddl
mysqlcheck -A 当前数据库的所有的表
which mysqlcheck
mysqlrepair test t1
模拟损坏表（索引没有了）
mv test.MYI /root/
mysqlcheck mydbname test 会出现说文件不存在
修复如下：
mysqlrepair mydbname test 还是不可以修复的
check table test EXTENEDED;
help repair table
repair table test use_frm;
check table test EXTENEDED; 这个时候就正常了
Myisamchk: 尽量这些表没有被写的时候操作
myisamckh --help | more
myisamchk -r -o test.myi
如果表正在操作，我们建议用如下的：
use mysql
check table t1;
optimize table t1;
mysql>\s
myisampack： 压缩表用的
man myisampack ( look help )
解压：
myisamchk -u my/test.MYI
myisampack --test my/test
select * from test limit 1,1;
压缩后，他就变成只读的表了。
myisamchk -rq my/test.MYI(mysql5.0 对空表会提示不需要压缩，之后的版本是可以压缩的)
Innodb: 事务性引擎（InnoDB Falcon）
innodb_data_file_path=datefile_spec1[;datafile_spec2]... 表空间的位置指定
innodb_data_home_dir
mysql>show engines;
看培植文件：
cat /usr/my.cnf | grep innodb
show variables like 'innodb%';
start transaction;
update t1 set balance=balance-1000 where where id=21345;
update t1 set balance=balance+1000 where where id=12345;
commit;
ACID:atomicity consistency isolation duration
isolation: read uncommitted, read committed,repeatable read--mysql default(可重复读), seriliable(串行读)
MVCC：multiversion concurrency control 多版本并发控制
begin,beginwork,start transaction;
commit;
ROLLBACK;
--ddl cannot be rolled back
Savepoints; 让事务回到某个点
AUTOCOMMIT;
Innodb官方：
OLTP oriented
performance ,reliability,scalability
emulated the Oracle architecture
page 16k默认大小 相当于oracle block
InnoDB on Disk Format:
InnoDB database files
InnoDB tablespaces
InnoDB pages/extents
InnoDB rows
InnoDB indexes
InnoDB logs
show variables like 'datadir%'; 默认数据位置
每个表只有两个文件
innodb_file_per_table =OFF|ON 如果是OFF 则在ibdata1（系统表空间,即共享表空间） files路径下，这样的话，ibdata 就会很大，如果不让数据文件和索引文件不要在ibdata1表空间里，则可以增加个培植文件中
/usr/my.cnf
default_storage_engine=innodb
innodb_file_per_table=1 or ON
数据文件和索引文件是:t1.ibd
show variables like 'innodb_data%';
也可以设置多个ibdata1 表空间，表空间由多个文件组成的
/usr/my.cnf
innodb_data_file_path=ibdata1:100M;ibdata2:10M:autoextend
重新启动mysql server
show engines; 是看不到innodb的
回出错的，需要重新修改会10M重新启动就可以了
show engines; 这个是时候是可以看到innodb的
tail -f -30
如果innodb_file_per_table =ON的话，ibdata files只是记录:internal data dictionary|insert buffer|undo logs信息的。一些表的数据和索引信息是不会写到此表空间的
The page size is always 16KB in uncompressed tablespaces, and 1KB-16KB in compressed tablespaces(for both data and index)
System Tablespace includes:
internal data dictionary
undo
insert buffer
doublewrite buffer
MySQL Replication info
Talbespace <--segment <-- extent <-- page <--row
an page=16KB
an extent=64 pages
多版本：获取和释放锁。就是更新的时候即要保存更新前(undo)的状态，也要保存新的值(redo)。等commit的时候
就安全的写到redo里。supports row lock
unlimited row-level locking 可以所很多行
multi-version read-consistency. 把旧值防到undo里，供其他的session一致性读。
mysql> begin;
mysql>update ttt set name='bill' where id=2;
mysql>commit;
update lost;更新丢失
intention locks 意象锁
共享锁SL，排它锁XL
Lock type compatibility:
X IX S IS
X N N N N
IX N Y N Y
S N N Y Y
IS N Y Y Y
Auto-Increment Locking:
1,Innodb uses a table-level 'auto-increment lock'
2,Table-level lock occurs at time of INSERT
3,Lock is released at statement end,not transaction end
所以就是一个bottleneck when 并发大于10的时候
5.1.22 version
innodb_autoinc_lock_mode=2 but not safe with statement-based replication or recovery scenarios.
row-based replication
statement-base replication
Phantoms vs consistency (幻读与一直性)
PHANTOM： a row that appears in a second query that was not in the first.
如何避免幻读：Gap Locking来解决，可以研究下
Innodb stores table locks in memory
select ... for update sets explict row locks.
show variables like 'autocommit%';
autocommit on 表示是自动提交，如果我需要自己提交，则需要显示的设置事务开始和结束。
start transaction /start work/begin
ddl dml
savepoint savename
commit;
rollback savepoint to savename;表示savename之后的操作全部回滚掉。
MySQL Lock:
Locks are used by a thread to keep other threads from modifying data in the area of the database.
Read Lock
Write Lock
dead lock
Table lock myiasm
|Page lock BDB
| Row lock innodb
show global variables like '%lock%';
show status like '%lock%';
注意table_locks_waited值
MyISAM, Memory only supports table lock.
lock tables t1 read|write
lock tables t1, t2 read|write
unlock tables 必须手动释放，如果手动加锁，所以手锁很危险的
help lock
必须要有lock tables权限才可以的
read lock自己和其他人都不能写
lock tables t1 read local; 自己不能写，别人可以写的，允许其他人在表的尾部写入数据。
unlock tables;
lock table t1 write 自己可以读，别人读等待
lock table t1 low_priority write;
show variables like '%low%';
low_priority_updates
show variables like 'max_write_lock_count%'; 多少写量后，可以进行读了。
Innodb lock:
row-locking 是基于innodb engine 的
table-locking 是基于mysqld的。
next-key locking
gap locking
insert intention gap locking
show status like 'innodb_row_lock%' 看统计信息的
show variables like 'innodb_lock%' 相关的参数信息
S-SHARED
X-exclusive
IS Intention Shared 意向共享锁
IX Intention Exclusive 意向排它锁
显示发起锁
IX
select ...for update
IS
select ... lock in share mode
select * from lt where id=100 lock in share mode;
show errors;
show engine innodb status\G
innotop 见控锁的工具
INNODB 回自动的帮表加个索引，如果没有创建索引的话
prompt (session 1 \u@\h [\d]) (\R:\m:\s)>
set session|global transaction level read uncommitted|read committed
select @@session.tx_isolation;
read uncommitted 会发生脏读
Phantom reads: 幻读 同一个事务中，不同的时间，读的信息不一样。
脏读和幻读区别不明白
DDL 是隐式的自动提交。
Replication:
Master:
-changes data
-Has binlog enabled
-Pushes binlog events when needed
Slave:
-Ask master for replication logs
-Get binlog event from master
-control point of replication
Binary log:
synchronous replication (commit后就复制)
-a transaction is not committed until the data has been replicated (and applied)
-safer, but slower
-in mysql cluster
asynchronous replication
-a transaction is replicated after is has been committed
-faster,but you can in some cases loose transactions if master fails
-easy to set up between mysql servers
semi-sync replication (5.5 version)
-a transaction is replicated after is has been committed on master and at least one slave receipt of all replication events for the transaction
-Be used to protect user transaction when error occur in asynchronous replication
http:www.foxitsoftware.com
How to configure Master- Slave replication
Mater configuration--required
-Log_bin
-server-id
Slave configuration -required
-server_id
-different from master's server_id
optional items:
master:
-binlog-do-db 明确指定复制那些数据库，那些数据库不复制，当有许多数据库的时候
(eg:
binlog-do-db=db1
binlog-do-db=db2
)
-binlog-ignore-db
slave:
-replicate-do-db
-replicate-ignore-db
-replicate-do-table
-replicate-ignore-table
-replicate-wild-do-table
-replicate-wild-ignore-table
-read-only (root用户还是可以修改的)
-log-slave-updates 可以做其他slave的master,它自己即是slave也是master,但是用的场景不多
-skip-slave-start master 启动的时候随即启动
configuration -grants on master
grant replication slave on *.* to (必须是*.* 所有库所有表的权限)
'repl_user'@'repl_host' identified by 'repl-pass';
DEPLOY(1)
step1: master
-check log_bin if turn on
-check server_id
step2: make a backup of the master
-online or offline backup
DEPLOY(2)
step3:slave
1,restore the backup to slave
--mysql<
--others
2,specified the point where start to replicate
-CHANGE MASTER TO xxxx
3,startup slave to catch up with master
-start slave
4, check slave status
-show slave status\G
CHANGE MASTER TO
1,used on slave
2,requires super privileges
3,configures the slave server connection to the master
4,slave should not be running
5,the user need replication slave privileges on master
change master to
master_host='192.168.92.100'
master_user='repl'
master_password='repl_pwd'
start slave | stop slave
1,used on slave
2,used to start or stop the slave threads
3,defaults to affecting both I/O and SQL thread
4,..but individual threads can be started or stopped
5,start slave SQL_THREAD
6,START SLAVE IO_THREAD
复制和engine无关：
innodb to innodb
innodb to myisam
myiasm to innodb
memory to myisam
one master to many slaves
master slave ---master slave 主库备库 主库备库
show master status\G
show binary logs;
used on master;
requires super privileges;
show binlog events
used on master
requires super privileges;
show slave hosts
used on master
requires super privileges;
show list of slaves currently registered with the master
only slaves started with report-host option are visible
purge binlog logs
used on master
expire_log_days
SQL_LOG_BIN
set SQL_LOG_BIN
used on master
requires super privileges
session variable
controls logging to binary log
does not work for NDB
mysql>set SQL_LOG_BIN=0;
mysql>insert into t1 values(1,2,3);
mysql>set sql_log_bin=1;
EXPIRE_LOGS_DAYS
0 means "never expire"
used on master
requires super privileges
positive value means expire logs after this many days
logs will be removed at startup or binary log rotation
can be used with running slave
logs are removed ! make sure you have backup!
RESET MASTER
used on master
requires reload privileges
deletes all binary logs in the index file
resets binary log index
used to get a "clean start"
use with caution!you lose data!!!
SHOW SLAVE STATUS
used on slave
requires super or replication client privileges
RESET SLAVE
used on slave
removes all info on replication position
-deletes master.info ,relay-log.info and all relay logs
relay logs are unconditionally removed!!!
..even if they have not been fully applied
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
used on slave
global sever variable
requires super privilege
HA:双master
HA OVERVIEW
HA : HIGH availability
SPOF: single point of failure
Failover: switch over automatically
switch over---manually
MySQL HA solution
MySQL replication
MySQL DRBD
MySQL with shared storage
MySQL cluster
24*365*60*60*(1-0.99999)
MYSQL Replication
Master sever:
changes data
keeps log of changes
slave server:
ask master for events
executes events
Synchronouse replication
1,data is replicated and appllied then committed
2,provides consistency ,but slower
3,provided by MySQL Cluster
Asynchronous replication
1,transactions committed immediately and replicated
2,no consistency,but faster
3,provided by MySQL Server
SemiSyncReplication
1,provided by Google
Binary log
1,log every change (select 是不记录的，只记录改变的)
2,split into transaction groups
File: master_bin.NNNNNN
1,The actual contents of the binlog
File: master_bin.index
1,an index file over the files above
Master: I/O thread
Slave: I/O thread and SQL Thread
master.info contains:
Read coordinates:
-master log name and master log position
Connection information:
-host,user,password ,port
-SSL keys and certificates
relay-log.info contains:
Group master coordinates:
-master log name and master log position
Group relay log coordinates:
-relay log name and relay log position
Steps:
1,Fix my.cnf file for master and slave
2,add user and grants on master
3,take backup of master
4,bootstrap slave from backup
5,configure slave
6,start slave
7,check slave status show slave status\G
Master configuration --required(必选择)
log_bin
server_id
slave configuration --required(必选择)
server_id
--different from master's server_id
show variables like 'server%';
show variables like 'log%'; 看log_bin是否开启用
show grants for repl@192.168.92.100;
设置全局锁：
flush tables with read lock;
show master status; 记住位置
unlock tables;
show variables like 'datadir%';
show grants;
MYSQL data dictionary: information_schema 此数据库不能更新的
它store database metadata
CHARACTER_SETS 当前数据可以支持的字符集
collations校对字符集
collation_character_set_applicability
tables: 包含数据库中所有的表
select concat('grant select ,insert on ',table_schema,'.', table_name ,' to abc@localhost;') from
tables where table_schema='sakila' and talbe_name like '%actor%';
COLUMNS 表
statistics 表的索引的统计信息
show index dbname.tbname;
user_privileges 用户级别的权限
schema_privileges 库级别的权限
table_privileges 表级别的权限
column_privileges 列级别的权限
table_constraints 表之间的关系
KEY_COLUMN_USAGE
ROUTINES
VIEWS
TRIGGERS
提升MYSQL的安全性
目录权限
mysql_secure_installation
mysql>\! ls -lt /var/lib/mysql
skip-networking
bind-address=127.0.01
禁止local infile local_infile=0
show variables like '%local_infile%'
vi /tmp/tt
1 china
2 usa
3 XXX
mysql>set @@global.local_infile=1;
mysql>load data local infile '/tmp/tt' into table tt;
我的命令会记录在： ~/.mysql_history
数据传输：
SSL
-Openssl
-Yassl
show variables like '%ssl%';
备份的安全
-加密
-放银行
-放专门的备份机
拥护相关的
user@ip or host
权限最小化
保护好你的root或改名
rename user root@127.0.0.1 toadmin@127.0.0.1
show grants for admin@127.0.0.1
删除空用户或空密码
定期检查用户，对用户进行登记
MysqlRoles工具from google
SQL INJECTION SQL注入
|
自学PHP网专注网站建设学习,PHP程序学习,平面设计学习，以及操作系统学习
京ICP备14009008号-1@版权所有www.zixuephp.com
网站声明：本站所有视频，教程都由网友上传，站长收集和分享给大家学习使用，如由牵扯版权问题请联系站长邮箱904561283@qq.com
