mysql

强制linux不记录敏感的历史命令#/root/.mysql_history 本也登入mysql执行的mysql命令记录
HISTCONTROL=ignorespace
mysql启动方法
/etc/init.d/mysqld start
/opt/local/mysql/bin/mysqld_safe &>/dev/null & #多实例 /opt/local/mysql/bin/mysqld_safe --defaults-file=/opt/local/data/3306/my.cnf &>/dev/null &
mysql关闭方法
/etc/init.d/mysqld stop
mysqladmin -uroot -ppassword shutdown #多实例加-S mysql.sock
mysql密码更改方法：
1、mysqladmin -uroot -poldpassword password 'newpassword' 多实例后面加-S /opt/local/data/mysql.sock
2、登入mysql update mysql.user set password=password("newpassword") where user='root' and host='localhost' flush privileges;
3、登入mysql set password=password('newpassword') #只能登入当前用户的密码
mysql密码忘记了：
1、#先关闭mysql
/etc/init.d/mysqld stop #多实例 kill killall
2、#用未启动授权表的方法启动mysql
/opt/local/mysql/bin/mysqld_safe --skip-grant-tables --user=mysql & #多实例加--defaults-file
3、修改密码
update mysql.user set password=password("newpassword") where user='root' and host='localhost' flush privileges
4、关闭mysql
mysqladmin -uroot -pnewpassword shutdown#多实例加-S mysql.sock
5、启动mysql
/etc/init.d/mysqld start
mysql -uroot -pnewpassword

create database howhy;
show create database howhy;
create database charsetdb default character set gbk collate gbk-chinese_ci #在编译时若没有指定默认字符集则可以这样指定字符集
create database charsetutf8 character set utf8 collate utf8_general_ci;
企业中如何确定字符集：
根据开发程序的字符集 在编译时指定特定的字符集
show databases like '%set%';
查看当前使用的数据库 select database(); use dbname;select version();select user();select now();
drop database dbname;
show tables;
drop user 'user'@'host'
show grants for 'user'@'host';#查看用户权限
create user 'user'@'host' identified by 'password' #默认权限只有连接权限USage
grant all on db.dbtable to 'user'@'host'
授权用户远程连接：grant all on *.* to 'user'@'ip.%' identified by 'password' mysql -uuser -ppassword -h ip
revoke 权限 on db.dbtable from 'user'@'host';
create tabel tablename(
id int(4) not null auto_increment,
name char(10) not null,
age int(2),
dep varchar(16) default null,
primary key(id),
key index_name(name)
);
alter table student change id id int primary key auto_increment
alter table student drop index index_name;
alter table student add index stu_index(name);
create [unique] index index_name on student(name)
show index from student\G
create index index_name on student(name,id)
drop index index_name on studnet;
索引会占用系统资源 ，更新数据库时还需维护索引 读多的数据可以建立索引 写多的最好不要建立索引
mysqldump -uroot -ppasswd -B dbname >/opt/dbname_bk.sql 只备份一个数据库
mysqldump -uroot -ppasswd -A　-B > /opt/db_bk.sql 备份所有数据库
explain select * from student where name='howhy'\G #可以查看根据查找的行数 检查是否走索引了
mysql -U -uroot -password 连接进去后可以防止update时必须要加条件
alter table student add sex char(4);
alter table student add dep char(10) after age;
rename table oldtablename to newtablename;
mysql乱码：
set name defaultcharset#临时设置字符集 然后再插入数据
mysql> source my.sql 可以从.sql文件中将数据导入数据库
永久修改在my.cnf中
[client]
default-character-set=utf8
[mysqld]
@default-character-set=utf8 #就是客户端 服务器都设置成相同的字符集
character-set-server=utf8

show character set;
show variables like 'character%';
my.cnf 中字符集优先级高于linux系统中字符集若my.cnf中设置了服务器和客户端字符集则linux系统中的字符集就不会影响mysql
| character_set_client | utf8 #可以由linux系统的字符集决定 若修改了linux系统的字符集或my.cnf中[client] default-character-set=utf8也会随之修改 |
| character_set_connection | utf8 #可以由linux系统的字符集决定 若修改了linux系统的字符集也会随之修改 |
| character_set_database | utf8 #my.cnf [mysqld] |
| character_set_filesystem | binary |
| character_set_results | utf8 #可以由linux系统的字符集决定 若修改了linux系统的字符集也会随之修改 |
| character_set_server | utf8 #my.cnf [mysqld] |
| character_set_system | utf8

mysql主从

主库操作：
先开启bin-log并设置server-id
受权复制账号：grant replication slave on *.* to 'rep'@'192.168.0.%' identified by 'pwd'
锁表 flush tables with read lock(锁表后无法写入）
记录主库show master status （file和 position）
备份主库：mysqldump -u root -p'pwd'-A -B --events|gzip >/opt/masterdb_$(date +%F).sql.gz
解锁 unlock tables;
从库操作：
关闭从库bin-log并设置server-id
把主库备份数据迁移到从库 gzip -d masterdb_$(date +%F).sql.gz
mysql -u root -p < masterdb_$(date +%F).sql
cat|mysql -u root -p''<<EOF
change master to
master_host='10.10.10.23',
master_port=3306,
master_user='repuser',
master_password='howhy123',
master_log_file='mysql-bin.000002',
master_log_pos=330;
EOF

stop slave
start slave
show slave status查看
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000014',MASTER_LOG_POS=120;
主从备份原理：
主库需IO线程 并开启binlog
从库需IO mysql线程 从库开启同步
change master to
master_host='10.10.10.23',
master_port=3306,
master_user='repuser',
master_password='howhy123',
master_log_file='mysql-bin.000002',
master_log_pos=330;
会通过io线程主动联系master io线程 master验证通过后会将binlog 发给slave slave会将binlog日志记录到relaylog里并记录master-info

mysql 锁表时间与这两个变量参数show variables like '%timeout%'有关：interactive_timeout         | 28800         wait_timeout                | 28800  更改set global wait_timeout=10

如果从库还想要级联从库需先开启bin-log 和log-slave-updates

 查看主从库同步状态 show processlist：

master:

mysql> show processlist\G
*************************** 1. row ***************************
Id: 10
User: repuser
Host: 10.0.0.17:41070
db: NULL
Command: Binlog Dump
Time: 6783
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 2. row ***************************
Id: 12
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
2 rows in set (0.00 sec)

从库：

mysql> mysql> show processlist\G
*************************** 1. row ***************************
Id: 7
User: system user
Host:
db: NULL
Command: Connect
Time: 6719
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 8
User: system user
Host:
db: NULL
Command: Connect
Time: 2524
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 9
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
3 rows in set (0.00 sec)

  将从库在my.cnf [mysqld]中设置read-only不会影响主从复制 但super user用户还是可以在从库进行增删改

  若主从同步冲突时若数据不重要时可以忽略同步的错误 方法如下：

stop slave

set global sql_slave_skip_counter=1

start slave 也可以在主库my.cnf中忽略不重要的错误代码这样同步发生这样的错误时还可以继续同步[mysqld] slave-skip-errors=1007,1032,1062