2019年 · MySQL

mysql开启事件 event_scheduler

一、查看事件是否开启
MySQL [(none)]> SHOW VARIABLES LIKE ‘event_scheduler’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| event_scheduler | OFF |
+—————–+——-+
1 row in set (0.10 sec)

二、开启事件

MySQL [(none)]> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.06 sec)

MySQL [(none)]> SHOW VARIABLES LIKE ‘event_scheduler’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| event_scheduler | ON |
+—————–+——-+
1 row in set (0.00 sec)

三、my.cnf最后添加并保存
event_scheduler = 1

四、重启MySQL服务生效
/etc/init.d/mysqld restart

2019年 · MySQL

修改Mysql索引长度限制-ERROR 1709 (HY000) at line 162: Index column size too large. The maximum column size is 767 bytes.

系统环境:
root@host:~/software/zabbix-3.0.27/database/mysql# lsb_release -a
LSB Version: core-2.0-amd64:core-2.0-noarch:core-3.0-amd64:core-3.0-noarch:core-3.1-amd64:core-3.1-noarch:core-3.2-amd64:core-3.2-noarch:core-4.0-amd64:core-4.0-noarch:core-4.1-amd64:core-4.1-noarch:security-4.0-amd64:security-4.0-noarch:security-4.1-amd64:security-4.1-noarch
Distributor ID: Ubuntu
Description: Ubuntu 14.04.4 LTS
Release: 14.04
Codename: trusty

MySQL 环境配置:
Server version: mysqld Ver 5.6.27 for Linux on x86_64 (Source distribution)
MySQL [(none)]> show variables like ‘%character%’\G
*************************** 1. row ***************************
Variable_name: character_set_client
Value: utf8mb4
*************************** 2. row ***************************
Variable_name: character_set_connection
Value: utf8mb4
*************************** 3. row ***************************
Variable_name: character_set_database
Value: utf8mb4
*************************** 4. row ***************************
Variable_name: character_set_filesystem
Value: binary
*************************** 5. row ***************************
Variable_name: character_set_results
Value: utf8mb4
*************************** 6. row ***************************
Variable_name: character_set_server
Value: utf8mb4
*************************** 7. row ***************************
Variable_name: character_set_system
Value: utf8
*************************** 8. row ***************************
Variable_name: character_sets_dir
Value: /usr/local/mysql/share/charsets/
8 rows in set (0.00 sec)

出现问题:
root@host:~/software/zabbix-3.0.27/database/mysql# mysql -uroot -p zabbix30 < schema.sql
Enter password:
ERROR 1709 (HY000) at line 162: Index column size too large. The maximum column size is 767 bytes.
出现问题语句如下:vim schema.sql +162
CREATE TABLE `slideshows` (
`slideshowid` bigint unsigned NOT NULL,
`name` varchar(255) DEFAULT ” NOT NULL,
`delay` integer DEFAULT ‘0’ NOT NULL,
`userid` bigint unsigned NOT NULL,
`private` integer DEFAULT ‘1’ NOT NULL,
PRIMARY KEY (slideshowid)
) ENGINE=InnoDB;
CREATE UNIQUE INDEX `slideshows_1` ON `slideshows` (`name`);

解决办法:
(1)查看相关配置并作出如下设置
innodb_large_prefix = ON
innodb_file_format = Barracuda
innodb_file_per_table = ON
或者
MySQL [(none)]> show variables like ‘%innodb_large_prefix%’;
+———————+——-+
| Variable_name          |   Value |
+———————+——-+
| innodb_large_prefix | OFF    |
+———————+——-+
1 row in set (0.00 sec)
MySQL [(none)]> set global innodb_large_prefix=on;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> show variables like ‘%innodb_file_format%’;
+————————–+———–+
| Variable_name | Value |
+————————–+———–+
| innodb_file_format           |Antelope     |
| innodb_file_format_check |      ON      |
| innodb_file_format_max | Barracuda |
+————————–+———–+
3 rows in set (0.01 sec)
MySQL [(none)]> set global innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)
(2)修改建表语句,加入row_format=dynamic
CREATE TABLE `slideshows` (
`slideshowid` bigint unsigned NOT NULL,
`name` varchar(255) DEFAULT ” NOT NULL,
`delay` integer DEFAULT ‘0’ NOT NULL,
`userid` bigint unsigned NOT NULL,
`private` integer DEFAULT ‘1’ NOT NULL,
PRIMARY KEY (slideshowid)
) ENGINE=InnoDB row_format=dynamic;
CREATE UNIQUE INDEX `slideshows_1` ON `slideshows` (`name`);
(3)修改字段长度, varchar(255) 改为 varchar(191)
CREATE TABLE `slideshows` (
`slideshowid` bigint unsigned NOT NULL,
`name` varchar(191) DEFAULT ” NOT NULL,
`delay` integer DEFAULT ‘0’ NOT NULL,
`userid` bigint unsigned NOT NULL,
`private` integer DEFAULT ‘1’ NOT NULL,
PRIMARY KEY (slideshowid)
) ENGINE=InnoDB;
CREATE UNIQUE INDEX `slideshows_1` ON `slideshows` (`name`);

原因:
MySQL 索引只支持767个字节,utf8mb4 每个字符占用4个字节,所以索引最大长度只能为191个字符,即varchar(191),若想要使用更大的字段,mysql需要设置成支持数据压缩,并且修改表属性 row_format ={DYNAMIC|COMPRESSED}

参考:https://blog.51cto.com/13120271/2315189
https://www.cnblogs.com/kerrycode/p/9680881.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

2019年 · Linux

Linux增加/删除虚拟ip

在网卡eth1上添加一个虚拟ip 192.168.53.209
1.添加ip
root@ubuntu:~# ip addr add 192.168.53.209/32 dev eth0

2.查看添加的虚拟ip
root@ubuntu:~# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:83:a7:64 brd ff:ff:ff:ff:ff:ff
inet 192.168.53.208/24 brd 192.168.53.255 scope global eth0
valid_lft forever preferred_lft forever
inet 192.168.53.209/32 scope global eth0        #=============> 新增的IP
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe83:a764/64 scope link tentative dadfailed
valid_lft forever preferred_lft forever
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:83:a7:6e brd ff:ff:ff:ff:ff:ff
inet 172.16.10.151/24 brd 172.16.10.255 scope global eth1
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe83:a76e/64 scope link tentative dadfailed
valid_lft forever preferred_lft forever

3.测试网络是否可通
root@ubuntu:~# ping 192.168.53.209
PING 192.168.53.209 (192.168.53.209) 56(84) bytes of data.
64 bytes from 192.168.53.209: icmp_seq=1 ttl=64 time=0.038 ms
64 bytes from 192.168.53.209: icmp_seq=2 ttl=64 time=0.034 ms
^C
— 192.168.53.209 ping statistics —
2 packets transmitted, 2 received, 0% packet loss, time 999ms
rtt min/avg/max/mdev = 0.034/0.036/0.038/0.002 ms

4.删除虚拟IP
root@ubuntu:~# ip addr del 192.168.53.209/32 dev eth0

5. 设置开启自动添加虚拟IP
root@ubuntu:~# cat /etc/rc.local
#!/bin/sh -e
#
# rc.local
#
# This script is executed at the end of each multiuser runlevel.
# Make sure that the script will “exit 0” on success or any other
# value on error.
#
# In order to enable or disable this script just change the execution
# bits.
#
# By default this script does nothing.
ip addr add 192.168.53.209/32 dev eth0              #服务器启动添加虚拟IP
exit 0

2019年 · Linux

sqlplus连接远程数据库

方式一:简易连接,不用进行网络配置,其实就是tnsname.ora文件,但只支持oracle10G以上。
命令:sqlplus 用户名/密码@ip地址[:端口]/service_name [as sysdba]
示例:sqlplus sys/pwd@ip:1521/test as sysdba
备注:使用默认1521端口时可省略输入

方式二:进行网络配置 oracle9i和以前的版本
2.1图形化操作:Net Configuration Assistant–> 本地Net服务名配置–>添加->服务名->协议(选tcp)->主机名称->端口->完成。

2.2文本化操作:编辑$ORACLE_HOME/NETWORK/ADMIN/tnsnames.ora文件
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip或主机名称)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = 数据库的服务名称)
)
)
备注说明:红色为修改部分
PROTOCOL:客户端与服务器端通讯的协议,一般为TCP,该内容一般不用改。
HOST:数据库侦听所在的机器的机器名或IP地址,数据库侦听一般与数据库在同一个机器上,所以当我说数据库侦听所在的机器一般也是指数据库所在的机器。在UNIX或WINDOWS下,可以通过在数据库侦听所在的机器的命令提示符下使hostname命令得到机器名,或通过ipconfig(for WINDOWS) or ifconfig(for UNIX)命令得到IP地址。需要注意的是,不管用机器名或IP地址,在客户端一定要用ping命令ping通数据库侦听所在的机器的机器名,否则需要在hosts文件中加入数据库侦听所在的机器的机器名的解析。
PORT:数据库侦听正在侦听的端口.可以察看服务器端的listener.ora文件或在数据库侦听所在的机器的命令提

示符下通过lnsrctl status [listener name]命令察看。此处Port的值一定要与数据库侦听正在侦听的端口一

样。
SERVICE_NAME:在服务器端,用system用户登陆后,sqlplus> show parameter service_name命令察看。
======================================================================

如何保证客户端机器连接到Oracle数据库呢?

A.  客户端
1.在客户端机器上安装oracle的Oracle Net通讯软件,它包含在oracle的客户端软件中。
2.正确配置了sqlnet.ora文件
3.正确配置了tnsname.ora文件

B.  服务器端
1.保证listener已经启动 lsntctl start
2.保证数据库已经启动。 sql>startup
转自:https://www.cnblogs.com/fushou/p/7381813.html

2019年 · Linux

ubuntu上Oracle 客户端安装

root@localhost:~# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 12.04 LTS
Release: 12.04
Codename: precise
root@localhost:~# apt-get install libaio1
root@localhost:~# apt-get install alien
root@ad-webnode1:~# ll oracle-instantclient*
-rw-r–r– 1 root root 34850762 Jun 19 18:07 oracle-instantclient-basic-10.2.0.5-1.x86_64.rpm
-rw-r–r– 1 root root 290107 Jun 19 17:46 oracle-instantclient-devel-10.2.0.5-1.x86_64.rpm
-rw-r–r– 1 root root 794214 Jun 19 17:46 oracle-instantclient-sqlplus-10.2.0.5-1.x86_64.rpm
root@localhost:~# alien -i oracle-instantclient-basic-10.2.0.5-1.x86_64.rpm
root@localhost:~# alien -i oracle-instantclient-sqlplus-10.2.0.5-1.x86_64.rpm
root@localhost:~# alien -i oracle-instantclient-devel-10.2.0.5-1.x86_64.rpm
root@localhost:~# cat /etc/ld.so.conf.d/libc.conf
# libc default configuration
/usr/local/lib
/usr/lib/oracle/10.2.0.5/client64/lib
root@localhost:~# ldconfig
root@localhost:~# sqlplus sys/pwd@ip:1521/test as sysdba

参考:https://help.ubuntu.com/community/Oracle%20Instant%20Client