mysql数据库理论与实战-白红宇

mysql数据库理论与实战

阅读量：5767 次

发布时间：2019-06-18

本文共 10694 字，大约阅读时间需要 35 分钟。

数据库

主流：互联网公司用mysql5.5

mysql单实例源码编译安装5.5.32

cd cmake-2.8.8

./configure

#CMake has bootstrapped. Now run gmake.

gmake

gmake install

cd ../

1.3.2 依赖包

yum install ncurses-devel -y

1.4 开始安装mysql

1.4.1 创建用户和组

groupadd mysql

useradd mysql -s /sbin/nologin -M -g mysql

1.4.2 解压编译MySQL

tar zxf mysql-5.5.32.tar.gz

cd mysql-5.5.32

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \

-DMYSQL_DATADIR=/application/mysql-5.5.32/data \

-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \

-DENABLED_LOCAL_INFILE=ON \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_FEDERATED_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \

-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \

-DWITH_FAST_MUTEXES=1 \

-DWITH_ZLIB=bundled \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_READLINE=1 \

-DWITH_EMBEDDED_SERVER=1 \

-DWITH_DEBUG=0

#-- Build files have been written to: /home/oldboy/tools/mysql-5.5.32

提示，编译时可配置的选项很多，具体可参考结尾附录或官方文档：

make

#[100%] Built target my_safe_process

make install

ln -s /application/mysql-5.5.32/ /application/mysql

如果上述操作未出现错误，则MySQL5.5.32软件cmake方式的安装就算成功了。

cp mysql-5.5.32/support-files/my-small.cnf /etc/my.cnf

配置环境变量：

Echo ‘export PATH=/application/mysql/bin:$PATH’>>/etc/profile

Tail -1 /etc/profile

Source /etc/profile

Echo $PATH

初始化数据库

mkdir -p /application/mysql/data

chown -R mysql.mysql /application/mysql

chmod -R 1777 /tmp

/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=mysql

cp /home/weipeng/tools/mysql-5.5.32/support-files/mysql.server /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

/etc/init.d/mysqld start

netstat -anptu |grep 3306

mysql多实例的安装：

pkill mysqld

rm -f /etc/init.d/mysqld 删除单实例实际不删不会影响。

mkdir -p /data/{3306,3307}/data

[root@mysql1 ~]# tree /data

/data

├── 3306

│ └── data

└── 3307

└── data

4 directories, 0 files

[root@mysql1 tools]# rz

rz waiting to receive.

Starting zmodem transfer. Press Ctrl+C to cancel.

Transferring data.zip...

100% 3 KB 3 KB/sec 00:00:01 0 Errors

[root@mysql1 tools]# unzip data.zip

Archive: data.zip

creating: data/

creating: data/3306/

inflating: data/3306/my.cnf

inflating: data/3306/mysql

creating: data/3307/

inflating: data/3307/my.cnf

inflating: data/3307/mysql

[root@mysql1 tools]# cp data/3306/my.cnf /data/3306/

[root@mysql1 tools]# cp data/3307/my.cnf /data/3307/

mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &

mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 >/dev/null &

mysqladmin -u root -pweipeng -S /data/3306/mysql.sock shutdown

mysqladmin -u root -pweipeng -S /data/3307/mysql.sock shutdown

或者用老男孩写的shell脚本启动多实例mysql：

[root@mysql1 tools]# cd data

[root@mysql1 data]# ls

3306 3307

[root@mysql1 data]# cp 3306/mysql /data/3306

[root@mysql1 data]# cp 3307/mysql /data/3307

[root@mysql1 data]#

[root@mysql1 data]# tree /data/

/data/

├── 3306

│ ├── data

│ ├── my.cnf

│ ├── mysql

│ └── mysql_oldboy3306.err

└── 3307

├── data

├── my.cnf

├── mysql

└── mysql_oldboy3307.err

chown -R mysql.mysql /data

find /data/ -type f -name “mysql”|xargs ls -l

find /data/ -type f -name "mysql" |xargs ls -l

find /data/ -type f -name "mysql" |xargs chmod +x

初始化多实例数据库：

[root@mysql1 tools]# cd /application/mysql/scripts/

[root@mysql1 scripts]# ls

mysql_install_db

[root@mysql1 scripts]# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql

[root@mysql1 scripts]# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql

然后启动各实例数据库：

[root@mysql1 scripts]# /data/3306/mysql start

Starting MySQL...

[root@mysql1 scripts]# /data/3307/mysql start

Starting MySQL...

查看是否启动：

Starting MySQL...

[root@mysql1 scripts]# netstat -lnptu|grep 330

tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 35831/mysqld

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 35115/mysqld

多实例更改密码：

mysqladmin -u root -S /data/3306/mysql.sock password ‘weipeng’

多实例登录mysql：

[root@mysql1 3306]# mysql -S /data/3306/mysql.sock 指定sock才能登录

修改属主和权限：

find /data/ -type f -name "mysql" |xargs chmod 700

find /data/ -type f -name "mysql" |xargs chown root.root

多实例有密码后的登录：

mysql -S /data/3306/mysql.sock -u root -pweipeng

总结：先用mysql用户然后+x权限初始化数据库，然后修改用户为root，权限改为700，再登录。

vim里面的内容替换：跟sed有点想:%s#A#B#g,将A换成B

优雅关闭mysql

1、mysqladmin -uroot -pweipeng shutdown

2、/etc/init.d/mysqld stop

想登录时不被看到明文密码直接在命令前加个空格即可，如果想删除某条hostory记录例如第二条，可以history -d 2

强制linux不记录敏感历史命令：

#HISTCONTROL=ignorespace

修改mysql提示符：

prompt \u@mysql \r:\m:\s->

有密码修改密码：

mysqladmin -u root -pweipeng password ‘weikang’

在数据库内部更新密码，适合密码丢了找回来：

update mysql.user set password=password(456) where user=’root’ and host=’localhost’; --------password(456)此处是调用password函数叫明文密码456加密，否则明文的密码在mysql看来是不安全的不让登陆,更改无效。而且where条件必须加，否则容易把所有都改了。

在数据库内部还可以用如下方法，不适合密码丢了，只适合修改密码：

mysql> set password=password(‘weikang’);

修改密码后都要刷新：flush privileges;

改密码后关闭mysql方式：mysqladmin -uroot -p新密码 shutdown

是大多数关系数据库管理系统所支持的工业标准。

DDL：create、drop、alter

DML:数据操作语言、增删改

DCL:控制语言，grant或revokecommite、roback

mysql> create database oldboy;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| oldboy |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.03 sec

查看新建数据库创建过程：

mysql> show create database oldboy;

+----------+-----------------------------------------------------------------+

| Database | Create Database |

+----------+-----------------------------------------------------------------+

| oldboy | CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+-----------------------------------------------------------------+

1 row in set (0.00 sec)

创建不同字符集数据库：

连接数据库：use weipeng

查看当前数据库：select database（）；

操作案例1，创建weikang用户（还可通过create和grant配合创建），对test库具备所有权限，允许从localhost主机登录管理数据库，密码是weikang：

grant all privileges on test.* to weikang@localhost identified by ‘weikang’;

查看某用户的权限：

mysql> show grants for oldboy@localhost

-> ;

+---------------------------------------------------------------------------------------------------------------+

| Grants for oldboy@localhost |

+---------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |

| GRANT ALL PRIVILEGES ON `test`.* TO 'oldboy'@'localhost' |

+---------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

另一种创建用户及授权的方法：

1、mysql> create user oldgirl@localhost identified by 'oldgirl123';

Query OK, 0 rows affected (0.00 sec)

2、grant all on test.* to oldgirl@localhost;

远程授权及连接：

或者create user identified by ‘test’,但不能为

create user 4 identified by ‘test’

oracle里查看help方法：首先help index，它把需要帮助的主题目录列出来，然后比如选择show主题，就可以help show查看关系show的用法。

收回用户权限，一定要对上对应的用户权限的库：

如果是*.*，那是收不回权限的。

建表的方法：

案例：

ID自增，id主键，name为普通索引

添加表的字段,

更改表名：

用mysqldump在备份数据库时最好加上-B参数，因为-B是在备份时也备份创建数据库的过程命令，这样在还原时就不用手工创建数据库，然后还原数据了，直接如下即可：

master-date=1这个参数能够在从库恢复时从binlog日志哪个点开始恢复。

master-date=2这个参数能够在从库恢复时从binlog日志开始恢复。

用source还原备份的数据库：

当没有-B备份数据库，还原时需要加数据库名，否则不成功：

查看binlog日志不能用cat方式查看，而要用mysqlbinlog来查看：

从binlog日志提取想要的备份，比如只想把oldboy库提取出来，这么做的原因在于生产环境中有很多库，如果整个库恢复需要大量时间，而且没有破坏的数据库去恢复会被阻止，导致要恢复的也恢复不下去：

指定位置点恢复，生产环境用的多：

通过时间点恢复，生产环境用的少，因为时间相对模糊，容易出错：

在mysql里叫binlog，在my.cnf里叫log-bin：

百度搜索mysql集群高可用架构方案演变---------老男孩主讲

mysql主从复制有利于数据库脚骨的健壮性、提升访问速度和易用性。

主从同步是异步，没有解决同步一致的义务，做不到数据一条不丢。用双写、一分钟日志解决。

主从读写分离：大多网站是读多写少。所以从库要做的内容比较多，一般集群。

企业双主的少，因为写的少，放两设备浪费，另外可用监控主，一旦发现问题，可切换备为主，主宕机不能写在有限的时间内也可忍受，读不能宕机：

主从同步原理图：

主从复制最重要的是主要打开binlog，在my.cnf里将log_bin那行注释去掉。

在mysql里查看是否已开启binlog,从库binlog日志可开可不开，主的一定要开：

在主mysql库上建立从库可复制logbin日志的用户及相关权限：

mysql> grant replication slave on *.* to 'rep'@'192.168.247.%' identified by 'oldboy123';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges; 记得刷新

Query OK, 0 rows affected (0.00 sec)

然后备份数据库：

1、先锁表，不让写了，然后查看binlog点：

2、备份数据：

3、vi查看/opt/rep.sql,搜索CHANGE，可查看到位置点是否和show master status的一样，一样说明备份正确：

4、主库上解除锁表提供对外服务：

5、在从库上将备份的数据库导入：

[root@mysql1 ~]# mysql -uroot -pweipeng -S /data/3307/mysql.sock </opt/rep.sql

6、在从库进入mysql执行以下配置,打开changlog，主从成功后会在从库的主目录下生成relay-bin的log日志。

CHANGE MASTER TO

MASTER_HOST='192.168.247.78',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='oldboy123',

MASTER_LOG_FILE='mysql-bin.000005',

MASTER_LOG_POS=338;

7、在从库查看是否有master内容更新，cat /data/3307/data/master.info

[root@mysql1 data]# cat master.info

mysql-bin.000005

338

192.168.247.78

rep

oldboy123

3306

8、打开从库更新日志开关，进入mysql：

start slave;

9、查看从库状态，如果显示两个进程都yes那么即为主从成功：

10、在主库创建新数据库，查看备库是否同步更新：

3）真正的企业授权方案，只同步数据，不同步授权用户：

read-only的方式让从库不能写,在my.cnf里的mysqld模块里加上read-only即可:

当在主从数据库不一致时，会出现同步出错，其中已解决办法如下：

当数据库连接慢时，忽略名字解析：

从库开启binlog原因及方法：

开启方法如下,缺一不可：

log-bin = /data/3307/mysql-bin

log-slave-updates

expire_logs_days = 7 保留几天，不要用find查找来删除，可能会产生问题。

查看以上有没生效到主目录下，看有没有mysql-bin.index产生即可。

异步复制（Asynchronous replication）：

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。

全同步复制（Fully synchronous replication）：

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

半同步复制（Semisynchronous replication）：

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用

当主库出现问题时，会在从库中选择myster.info容量或POS最大的那个作为新的主库，因为它的内容最新，需要的bin-log日志也少些容易恢复些，另外如果整个主从同步是半同步机制，那么半同步里的从主机即刻接替服务：