分类目录归档:Mysql

MySQL行列变换

employee_id season score
339 2018-1  S
339 2018-2  A
339 2018-3  B
339 2018-4  S
400 2018-1  S
400 2018-2  A
400 2018-3  B
select employee_id,
max(case when season='2018-1' then score else null end) as '2018-1',
max(case when season='2018-2' then score else null end) as '2018-2',
max(case when season='2018-3' then score else null end) as '2018-3',
max(case when season='2018-4' then score else null end) as '2018-4'                
from employee  GROUP BY employee_id
employee_id 2018-1 2018-2 2018-3 2018-4
339 S   A   B   S
400 S   A   B   

这是今天碰到的面试题,并没有完全答得上来,而且这个方案也是不完美的。等有了更好的方法再编辑

0

Mysql5.7主从复制配置

Mysql使用3个线程来执行复制功能,其中1个在主服务器上,另2个在从服务器上,当执行start slave时,主服务器创建一线程负责发送二进制日志。从服务器创建一个I/O线程,负责读取主服务器上的二进制日志,然后将该数据保存到从服务器数据目录中的中继日志文件中。从服务器的SQL线程负责读取中继日志并重做日志中包含的更新,从而达到主从数据库数据的一致性。

实验环境:centos7.4+Mysql5.7,虚拟机使用virtualbox,虚拟机网络设置为桥接方式连网,主服务器ip:192.168.0.103,从服务器ip:192.168.0.101,两台虚拟机可以互相访问,Mysql开启二进制日志,确认两台服务器mysql服务已经开启并能提供服务

#主库my.cnf配置
[mysqld]
log_bin=binlog
server_id=1

#从库my.cnf配置
[mysqld]
log_bin=binlog
server_id=2

在主库192.168.0.103创建远程帐号分配给从库复制时使用,并授予replication slave权限.

mysql> CREATE USER 'rep'@'192.168.0.101' IDENTIFIED BY '123@.123';
mysql> grant replication slave on *.* to rep@192.168.0.101;

登录主库得到当前binlog的文件名和偏移量

mysql> show master logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       177 |
| binlog.000002 |      3200 |
+---------------+-----------+
2 rows in set (0.00 sec)

登录从库设置主备关系

mysql> change master to 
    -> master_host='192.168.0.103',
    -> master_port=3306,
    -> master_user='rep',
    -> master_password='123@.123',
    -> master_log_file='binlog.000002',
    -> master_log_pos=3200;

登当从库启动slave线程并检查同步状态

mysql> start slave;

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.103
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 3200
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 1339
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3200
              Relay_Log_Space: 1550
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 1f170097-5354-11e8-b7ad-0800272b97b5
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

确认Slave_IO_Running: Yes和Slave_SQL_Running: Yes,如果有一个为No,则根据错误提示解决,mysql5.7版本可能会报一个server_id冲突的问题。因为在其它目录下可能有一个配置文件覆盖掉/etc/my.cnf的server_id的配置,解决方法是找到这个配置文件,在里面配置一下server_id=2

[root@localhost mysql]# find / -name "*cnf*"
/etc/my.cnf.d
/etc/my.cnf
/usr/share/mysql/my-default.cnf

[root@localhost mysql]# vim /usr/share/mysql/my-default.cnf 

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
server_id = 2

验证主从复制是否成功 登录主库192.168.0.103

mysql> use testslv;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from user_info;
+----+-----------+
| id | user_name |
+----+-----------+
|  1 | damon     |
|  2 | connie    |
+----+-----------+
2 rows in set (0.00 sec)

mysql> update user_info set user_name='connie chen' where id =2;
Query OK, 1 row affected (0.12 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user_info;
+----+-------------+
| id | user_name   |
+----+-------------+
|  1 | damon       |
|  2 | connie chen |
+----+-------------+
2 rows in set (0.00 sec)

登录从库192.168.0.101验证

mysql> select * from testslv.user_info;
+----+-------------+
| id | user_name   |
+----+-------------+
|  1 | damon       |
|  2 | connie chen |
+----+-------------+
2 rows in set (0.00 sec)
1+

Centos7.4安装Percona XtraBackup

从Percona 存储库安装Percona XtraBackupyum
安装Percona存储库

您可以通过以root用户身份或使用sudo运行以下命令来安装Percona yum存储库 :

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

测试存储库

通过执行以下命令,确保软件包现在可以从存储库中获得:

yum list | grep percona

安装软件包

您现在可以运行以下命令来安装Percona XtraBackup:

yum install percona-xtrabackup-24

警告
为了成功安装Percona XtraBackup libev软件包将需要先安装。libev包可以从EPEL存储库安装 。

全备:

# innobackupex --user=root --password=pwd  /path/to/backup/
# innobackupex --apply-log  /path/to/backup #提交事务等保持数据一次性

还原:

# innobackupex --copy-back  /path/to/BACKUP-DIR

还原成功后,数据文件将在原文件夹内重新生成,需要手动恢复文件属性

chown -R mysql.mysql ./* 

重启服务:

# systemctl start mysqld.service
0

查看MySQL表的字符集和校对规则

mysql> show full columns from city\G;
*************************** 1. row ***************************
     Field: ID
      Type: int(11)
 Collation: NULL
      Null: NO
       Key: PRI
   Default: NULL
     Extra: auto_increment
Privileges: select,insert,update,references
   Comment:
*************************** 2. row ***************************
     Field: Name
      Type: char(35)
 Collation: latin1_swedish_ci
      Null: NO
       Key:
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 3. row ***************************
     Field: CountryCode
      Type: char(3)
 Collation: latin1_swedish_ci
      Null: NO
       Key: MUL
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 4. row ***************************
     Field: District
      Type: char(20)
 Collation: latin1_swedish_ci
      Null: NO
       Key:
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 5. row ***************************
     Field: Population
      Type: int(11)
 Collation: NULL
      Null: NO
       Key:
   Default: 0
     Extra:
Privileges: select,insert,update,references
   Comment:
5 rows in set (0.00 sec)

ERROR:
No query specified
0

关于MySQL视图

MySQL5.0版本之后开始引入视图。视图本身是一张虚拟表,不存放任何数据。在使用SQL语句访问视图的时候,它返回的数据是从其他表中生成的。视图和表是在同一个命名空间,MySQL在很多地方对于视图和表是同样对待的。不过视图和表也有不同,如不能对视图创建触发器,也不能使用drop table命令删除视图。
以下为简单创建视图的命令(示例数据库可在mysql官网下载):

mysql> create view Oceania as select * from Country where Continent='Oceania' wi
th check option;
Query OK, 0 rows affected (0.08 sec)

check option子句表示通过视图更新的行,都必须符合视图本身的where条件定义。所以不能更新视图定义列以外的行,比如上例中不能更新Continent列,也不能插入不同Continent值的新数据,否则会报错:

mysql> update oceania set Continent='Europe' where code ='GUM';
ERROR 1369 (HY000): CHECK OPTION failed 'world.oceania'

创建视图并指定视图使用临时表算法:

mysql> create algorithm=temptable view v2 as select * from sakila.actor;
Query OK, 0 rows affected (0.14 sec)

如果视图定义中包含了GROUP BY、UNION、聚合函数,以及其他一些特殊情况,就不能被更新了。更新视图的查询也可以是一个关联语句,但必须来自同一个表。另外,所有使用临时表算法实现的视图都无法被更新。MySQL并不会保存视图定义的原如SQL语句,所以如果打算通过执行show create view后再个性其结果的方法重新定义视图,可能会有点失望。show create view出来的视图创建语句将以一种不友好的内部方式呈现,充满了各种转义符,没有代码格式化,没有注释,也没有缩进。

1+

COUNT()的使用

  • COUNT()的作用
    COUNT()是一个特殊的函数,有两种非常不同的作用:它可以统计某个列值的数量,也可以统计行数。在统计列值时要求列值是非空的(不统计NULL)。如果在COUNT()的括号中指定了列或者列的表达式,则统计的不是这个表达式有值的结果数。因为很多人对NULL理解有问题,所以这里很容易产生误解。

COUNT()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时,实际上就是在统计行数。最简单的就是当我们使用COUNT(*)的时候,这种情况下通配符*并不会像我们猜想的那样扩展成所有的列,实际上,它会忽略所有的列而直接统计所有的行数。

  • 关于MyISAM的神话
    一个容易产生的误解就是:MyISAM的COUNT()函数总是非常快,不过这是有条件的,即只有没有任何where条件的COUNT(*)才非常快,因为此时无须实际去计算表的行数。MySQL可以利用存储引擎的特性直接获取一个值。如果MySQL知道某个列col不可能为NULL值,那么MySQL内部会将COUNT(col)表达式优化为COUNT(*)。

当统计带Where子句的结果集行数,可以是统计某个列值的数量时,MyISAM的COUNT()和其他存储引擎没有任何不同,就不再有神话般的速度了。

0

UNION的限制

有时,MySQL无法将限制条件从外层“下推”到内层,这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。

如果希望UNION各个子句能够根据LIMIT只取部分结果集,或者希望能够先排好序再合并结果集的话,就需要在UNION的各个子句中分别使用这些子句。例如,想将两个子查询结果联合起来,然后再取前20条记录,那么MySQL会将两个表都存放到同个临时表中,然后再取出前20行记录:

(select firstname,lastname 
from sakila.actor order by lastname)
union all
(select firstname,lastname
from sakila.customer
order by lastname)
limit 20;

这条查询将会把actor中的200条记录和customer表中的599条记录存放在一个临时表中,然后再从临时表中取出前20条。可以通过UNION的两个子查询中分别加一个LIMIT 20来减少临时表中的数据:

(select firstname,lastname 
from sakila.actor order by lastname
limit 20) union all
(select firstname,lastname from sakila.customer 
order by lastname limit 20)
limit 20;

现在中间的临时表只包包含40条记录了,除了性能的考虑之外,在这里还需要注意一点:从临时表中取出数据的顺序并不是一定的,所以如果想获得正确的顺序,还需要加一个全局的order by 和limit操作。

0