MySQL数据库基本操作

重点内容修改下列脚本mysql_practice.sql，进行练习mysql基本操作：

show databases;
create database if not exists `HA`;
use HA;
create table if not exists student (id int, name char(40), age int);
show create table student \G;
desc student;


create table student2 (id int(20), name char(40), age int) ENGINE=InnoDB DEFAULT CHARSET=utf8;
show columns from student2;
drop table student2;


alter table student rename students;
show tables;
alter table students modify id int(10);
show fields from students;
alter table students modify id int(20) primary key;
show fields from students;
alter table students change name stname char(20);
desc students;
alter table students add sex enum('M','W');
desc students;
alter table students add uid int(10) first;
desc students;
alter table students add address char(50) after age;
desc students;
alter table students drop address;


insert into students values(1,1,'liqiang',22,'M');
insert into students(uid,id) values(2,2);
insert into students values(3,3,'tttt',24,'W');


select * from students;
select uid,id from students;
select * from HA.students \G;

update students set stname='xyz',age=27 where id=1;
delete from students where id=1;

insert into students values(4,4,'liqiang',29,'M');
insert into students values(5,5,'liqiang',50,'W');
select distinct stname,age from students;
select distinct stname from students;

show tables;
drop table if exists students;
show tables;
show databases;
drop database if exists `HA`;
show databases;

2. 自动提交

禁止自动提交
SET autocommit=0;
启用自动提交
SET autocommit=1;

3. 大小写

默认不区分大小写
区分大小写查询，加入binary关键字：
select * from students where binary stname = 'xyz'

4. 排序

select * from students order by id desc;

5. 丢失密码

跳过授权表进入mysql：
方法一：修改my.cnf，[mysqld]加入如下
skip-grant-tables
方法二：关闭数据库，以mysqld_safe启动

service mysqld stop
mysqld_safe --skip-grant-tables

进入数据库后，默认是以read_only模式，加入如下命令才能修改数据库

> set global read_only=0;
> grant all privileges on *.* to slave15@'%' identified by '123456' with grant option;

6. 字符集问题

在mysql中默认字符集是latin1，
想要设置字符集为uft-8，可以在 my.cnf 文件中添加以下设置：

［client］
default-character-set=utf8
［mysqld］
default-character-set=utf8
skip-character-set-client-handshake #此处是忽略客户端的字符集，使用服务器的设置

（skip语句和client中的default选一个）

需要注意的问题是：

1. 在5.1中是default-character-set=utf8
2. 在5.5中是character-set-server=utf8
   引用自：http://www.xuejiehome.com/blread-1757.html

7. 配置默认引擎

[mysqld]
default-storage-engine=INNODB

8. 恢复数据库

load data infile '/tmp/res2' into table uPwd fields terminated by '----' lines terminated by '\n';

9. 主从配置

M-S
(首先手动还原主和从数据库，使他们保持一致)
M:
my.cnf

server-id=1                 #数据库id标识
log-bin=mysql-bin-master    #启用二进制日志
binlog-do-db=HA             #指定同步数据库
binlog-ignore-db=mysql      #指定忽略数据库
binlog-ignore-db=information_schema

添加授权用户

grant replication slave on *.* to slave@192.168.1.5 identified by "123456";

查看状态

show master status;

查看事件

show binlog events \G

S:
my.cnf

server-id=2             #数据库标识，唯一

如果mysql版本在5.1以下（包括5.1），还需配置如下：
master-host=192.168.1.5
master-user=slave
master-password=123456
#master-port=3306
master-connect-retry=60
如果mysql版本在5.1以上，则进入数据库中
change master to master_host="192.168.1.4",master_user="slave",master_password="123456";
启动从数据库同步
start slave;
查看从状态
show slave status \G
修改同步配置

stop slave;
change master to master_log_file='mysql-log-master.00001',master_log_pos=106;
start slave;

MM
M1与M2类似，首先保持数据库中数据一致，结构一致
my.cnf

log-bin=mysql-bin-slave1
binlog-do-db=HA
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
server-id=1         #两台机器上的id不同

分别创建用户用户主从同步，并分别修改两台数据库中的master为对方

M-S-S
首先保证数据库中数据与结构一致
M:
my.cnf

server-id=1
log-bin=mysql-bin-master
sync-binlog=1
binlog_format=row
binlog-do-db=HA
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

S1(中继)
(在S1上可以修改数据库引擎为blackhole，可以做到不保存数据，提高性能)
my.cnf

server-id=2
log-bin=mysql-bin-slave1
log-slave-updates=1
binlog_format=row

S(从)
my.cnf

server-id=3
log-bin=mysql-bin-slave2
binlog_format=row

9. 读写分离

通过主从复制(Master-Slave)的方式同步数据，在通过MySQL-Proxy来提升数据库的并发负载能力。
基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

---

连接Mysql Server出错

---

错误信息	解决方法
ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.85' (111)	查看在my.cnf中是否设置了skip_networking ,查看在my.cnf中是否设置了bind_address=127.0.0.1
ERROR 1130 (HY000): Host '10.0.1.151' is not allowed to connect to this MariaDB server	在数据库中未分配远程访问的权限 grant all privileges on *.* to 'root'@'%' identified by 'xx' with grant option;