mysql数据库理论与实战

leonheart

　　数据库

　　主流：互联网公司用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]#
　　[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>　　查看某用户的权限：
　　mysql> show grants for oldboy@localhost
　　-> ;
　　+---------------------------------------------------------------------------------------------------------------+
　　| Grants for oldboy@localhost                                                                                   |
　　+---------------------------------------------------------------------------------------------------------------+

　　| GRANT USAGE ON *.* TO 'oldboy'@'localhost'>　　| GRANT ALL PRIVILEGES ON `test`.* TO 'oldboy'@'localhost'                                                      |
　　+---------------------------------------------------------------------------------------------------------------+
　　2 rows in set (0.00 sec)
　　另一种创建用户及授权的方法：

　　1、mysql> create user oldgirl@localhost>　　Query OK, 0 rows affected (0.00 sec)
　　2、grant all on test.* to oldgirl@localhost;
　　远程授权及连接：

　　或者create user test@10.1.1.0/255.255.255.0 identified by ‘test’,但不能为
　　create user test@10.1.1.0/24   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.%'>　　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  show full processlist;
　　然后查看是否有建索引：

　　不重复值最多的上面创建索引，where后有=的走索引效率最高：
　　create index d_a_p ad_oldboy_detail(dateline,ader(20),pos(20));

　　数据库不适合做搜索，有相关软件适合做，比如Sphinx，Xapian（大公司会自己开发类似百度的小规模内部搜索引擎）：
　　cpu高 strace 死循环