Category: MySQL

mysql绑定多个ip地址
my.cnf中有选项bind-address=127.0.0.1，是说mysql server监听的是本地发来的请求，如果是任意主机都可以请求，则写为0.0.0.0，但是这样又不太安全。监听某ip，指定此ip地址即可，但是要保证mysql的user中有允许此ip访问，否则不能对数据库操作。那么是否可以在配置里只规定几个ip呢？

简单直接回答：不可能

请参考：http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_bind-address

The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces. The default address is 0.0.0.0. To specify an address explicitly, use the –bind-address=addr option at server startup, where addr is an IPv4 address or a host name. If addr is a host name, the server resolves the name to an IPv4 address and binds to that address. The server treats different types of addresses as follows:

If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.
If the address is a “regular” IPv4 address (such as 127.0.0.1), the server accepts TCP/IP connections only for that particular IPv4 address.

但是有此需求，就会到访问控制，那么使用防火墙iptables可实现此效果

mysql-server为192.168.1.3，只允许192.168.1.4, 192.168.1.5, 192.168.1.6来访问3306端口

在my.cnf中
```
bind-address = 0.0.0.0
```
在访问3306端口的主机中，只允许192.168.1.4-6，其他ip一律DROP掉
```
/sbin/iptables -A INPUT -p tcp -s 192.168.1.4 --dport 3306 -j ACCEPT
/sbin/iptables -A INPUT -p tcp -s 192.168.1.5 --dport 3306 -j ACCEPT
/sbin/iptables -A INPUT -p tcp -s 192.168.1.6 --dport 3306 -j ACCEPT
/sbin/iptables -A INPUT -p tcp --dport 3306 -j DROP
```
或
```
/sbin/iptables -A INPUT -p tcp --dport 3306 ! -s 192.168.1.4 -j DROP
/sbin/iptables -A INPUT -p tcp --dport 3306 ! -s 192.168.1.5 -j DROP
/sbin/iptables -A INPUT -p tcp --dport 3306 ! -s 192.168.1.6 -j DROP
```
保存防火墙规则
```
service iptables save
```
查看INPUT链包含3306的规则
```
echo -e "target prot opt source destination\n$(iptables -L INPUT -n | grep 3306)"
```
这样就实现了mysql只允许指定ip访问。

参考：

http://www.cyberciti.biz/faq/unix-linux-mysqld-server-bind-to-more-than-one-ip-address/
2013 年 7 月 1 日
mysql主从复制
大致思路：设置master端，用于复制的账户，锁表导入数据库，在slave中导入数据库，设置slave端

mysql主从复制是一种比较靠谱的备份方式，这里用最简单的，一个master，一个slave

12.34.56.789- Master Database

12.23.34.456- Slave Database

前提条件：

主从数据库大版本要一样，可以用mysql -V查看，我这里用的是mysql Ver 14.14 Distrib 5.5.31

设置master端

配置文件vim /etc/mysql/my.cnf，mysql的bind-address只能绑一个特定的ip或0.0.0.0，我现在用的是0.0.0.0，即
```
bind-address            = 0.0.0.0
```
在 [mysqld]段下为server-id设置一个独立的编号
```
server-id               = 1
```
主从复制需要binary的log日志，将log_bin这一行取消注释
```
log_bin                 = /var/log/mysql/mysql-bin.log
```
设置要复制的数据库jpuyydb
```
binlog_do_db            = jpuyydb
```
master的配置文件设置完了，重启mysql
```
/etc/init.d/mysql restart
```
接下来在mysql中操作，mysql -u root -p，添加一个复制用的账户slave_user密码是password，这里的权限是可以复制所有数据库
```
GRANT select, replication client, replication slave *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
```
刷新权限
```
FLUSH PRIVILEGES;
```
接下来的操作需要按步骤操作，将jpuyydb锁表，防止有其他操作影响当前状态
```
use jpuyydb;
flush tables with read lock;
```
接下来查看master状态，并将下面的值file和position记录下来
```
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | jpuyydb | test |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
```
如果在当前的界面有一些操作的话，数据库的表会自动解锁，所以新开一个窗口，将数据库备份下来，
```
mysqldump -u root -p --opt jpuyydb > jpuyydb.sql
```
然后在原窗口解锁，退出，master端就设置好了
```
UNLOCK TABLES;
QUIT;
```
在slave中创建并导入数据库，

在mysql中创建数据库
```
create database if not exists jpuyydb default charset utf8 collate utf8_general_ci;
```
导入数据库
```
mysql -u root -p jpuyydb < jpuyydb.sql
```
设置slave端，vim /etc/mysql/my.cnf，需要修改的如下
```
server-id               = 100
```
添加一条relay-log
```
relay-log               = /var/log/mysql/mysql-relay-bin.log
```
```
log_bin                 = /var/log/mysql/mysql-bin.log
```
```
binlog_do_db            = jpuyydb
```
修改好之后退出，重启mysql，之后进入mysql控制界面，运行
```
CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;
```
现在slave设置好了，启动slave
```
START SLAVE;
```
查看slave的状态，\G将输出以适合阅读的方式显示出来
```
SHOW SLAVE STATUS\G
```
如果下面两条都是Yes表示成功
```
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
```
Trouble Shooting:

如果一直是连接状态，需要做如下排查，

在slave端尝试连接master
```
mysql -u slave_user -p -P 3306 -h 12.34.56.789
```
在master端查看3306的连接情况，应该有一条类似于下面的ESTABLISHED的记录
```
lsof -i tcp:3306
```
```
mysqld  10310 mysql   15u  IPv4 7636301      0t0  TCP ip-10-128-.internal:mysql->112.65.13.1s:55781 (ESTABLISHED)
```
在slave端如果一直是connecting状态，则尝试如下操作
```
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;
```
在slave端出现如下错误时

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

解决方法一：

进入到/var/lib/mysql/删掉

master.info
relay-log.info

解决方法二：
```
mysql> flush slave;

Query OK, 0 rows affected (0.00 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
```
参考：

https://www.digitalocean.com/community/articles/how-to-set-up-master-slave-replication-in-mysql
2013 年 7 月 1 日
mysql 中的 show命令
最早接触到的show，如show databases; show tables; 其实show还有更多的用法。

显示mysql中所有数据库的名称
```
show databases;
```
显示当前数据库中所有表的名称
```
show tables
或
show tables from database_name;
```
显示正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。
```
show processlist;
```
或
```
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
```
显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间
```
 show table status;
```
显示表中列名称，或者说是表的结构
```
show columns from table_name from database_name;
或
show columns from database_name.table_name;
```
显示一个用户的权限，显示结果类似于grant 命令
```
show grants for user_name@localhost;
```
显示表的索引
```
show index from table_name;
```
显示一些系统特定资源的信息，例如，正在运行的线程数量，mysql运行uptime(秒)
```
show status;
```
显示系统变量的名称和值
```
show variables;
```
查看默认存储引擎
```
SHOW VARIABLES LIKE 'storage_engine';
```
查看该数据库系统支持的存储引擎的类型：
```
SHOW ENGINES;
```
显示服务器所支持的不同权限
```
show privileges;
```
显示create database 语句是否能够创建指定的数据库
```
show create database database_name;
```
显示create database 语句是否能够创建指定的数据库
```
show create table table_name;
```
显示安装以后可用的存储引擎和默认引擎
```
show engines;
```
显示innoDB存储引擎的状态
```
 show innodb status;
```
显示最后一个执行的语句所产生的错误、警告和通知
```
 show warnings;
```
只显示最后一个执行语句所产生的错误
```
show errors;
```
查看时区
```
show variables like 'time_zone';
```
2012 年 12 月 20 日
mysql数据库空间用满时，巧用软链接转移
参考：http://www.webhostingtalk.com/showthread.php?t=1086919

对于一些做SEO的人来说，/var/lib/mysql 和 /var/www 是两个占用空间的大户，下面以数据库为例，将/var/lib/mysql下的数据库移动到较大的/home下，并使用软链接将/home/mysql 链接到/var/lib/mysql，实现充分利用空间的目的。

在这些网站都不在使用的时候，停用mysql，apache
```
/etc/init.d/mysqld stop
/etc/init.d/httpd stop
```
使用rsync完整的将数据拷贝（-a archive mode归档模式 -v verbos显示详细）
```
rsync -av /var/lib/mysql /home/
```
这个过程根据数据量的不同时间不同，数据很大的话建议使用screen

接下来看一下大小，如果以上命令都完全执行完的话，是不会出现什么问题的
```
du -sh /var/lib/mysql
du -sh /home/mysql
```
将旧的/var/lib/mysql移走，随便移到什么地方，实再不需要删掉也行
```
mv /var/lib/mysql /var/lib/mysql-bak
```
创建软链接
```
ln -s /home/mysql /var/lib/mysql
```
之后一定要再次确认上面的软链接链接到对的地方，如果不正确mysql一启动就麻烦了。

#######ubuntu用户注意 begin#######

如果使用ubuntu下apt-get安装的mysql版本，还需如下操作
```
vim /etc/apparmor.d/usr.sbin.mysqld
```
将如下两行注释
```
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
```
变更为（可能是因为apparmor不支持软链接）
```
/home/mysql/ r,
/home/mysql/** rwk,
```
重启apparmor
```
/etc/init.d/apparmor restart
```
#######ubuntu用户注意 end#######

之后启动mysql，apache
```
/etc/init.d/mysqld start
/etc/init.d/httpd start
```
这样以后所有的mysql数据的位置就在/home/mysql里了。

另一种解决方案：不用软链接而直接将数据库转移到目录/mnt/sx_disk
```
rsync -av /var/lib/mysql /mnt/sx_disk/
```
之后在/etc/my.cnf中，将[mysqld]中的如下参数改为
```
[mysqld]
datadir=/mnt/sx_disk/mysql
socket=/mnt/sx_disk/mysql/mysql.sock
```
只这样还不行，会出现
```
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
```
另外需添加
```
[client]
port=3306
socket=/mnt/sx_disk/mysql/mysql.sock
```
这样数据库就存为了新的位置/mnt/sx_disk目录中。

如果同是ubuntu下mysql的话，也需要按上面的方式修改/etc/apparmor.d/usr.sbin.mysqld
2012 年 9 月 8 日
加大mysql的max_allowed_packet值
今天在安装confluence的team calendars插件的时候，死活安装不上去，看日志文件提示

Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (4521890 > 1048576). You can change this value on the server by setting the max_allowed_packet’ variable.

所以加大mysql的max_allowed_packet值

可以编辑my.cnf来修改，在[mysqld]段或者mysql的server配置段进行修改。
max_allowed_packet = 20M

max_allowed_packet 参数的作用是，用来控制其通信缓冲区的最大长度
如果找不到my.cnf可以通过
```
mysql --help | grep my.cnf
```
mysql根据配置文件会限制server接受的数据包大小。

有时候大的插入和更新会被max_allowed_packet 参数限制掉，导致失败。

查看当前配置
```
show VARIABLES like '%max_allowed_packet%';
```
显示的结果为：

+——————–+———+
| Variable_name | Value |
+——————–+———+
| max_allowed_packet | 1048576 |
+——————–+———

以上说明当前的配置是：1M

后来改成20M，就成功安装了那个team calendars插件。
2012 年 6 月 27 日

mysql备份和还原数据库

备份服务器上所有数据库，加上–opt表示采用优化(Optimize)方式。

mysqldump -uroot -ppasswd --opt --all-databases > allbackupfile.sql

或

mysqldump -uroot -ppasswd -A > allbackupfile.sql

备份指定数据库

mysqldump -uroot -ppasswd databasename > /tmp/dbbackup.sql

备份指定的表，写在要备份的库后面

mysqldump -uroot -ppasswd databasename table1 table2 > /tmp/tables.sql

还原MySQL数据库的命令（还原表就不用加表名了）

mysql -hhostname -uusername -ppassword databasename < backup.sql

还原为utf-8

mysql -uusername -ppassword databasename --default-character-set=utf8 < backup.sql

mysqldump 加 where

mysqldump -uroot -ppasswd databasename table1 --where "table1.a=1" > /tmp/table.sql

使用–tab方式备份和还原数据库

首先将备份文件存在/home/jpuyy/blog，这时会生成以表名命名的.sql和.txt文件，.sql保存了表的创建语句，.txt保存了以默认分隔符的纯数据文本。

mysqldump -u root -p --tab=/home/jpuyy/blog database

接下来需要导入数据库时

mysqladmin create newdatabase
cat /home/jpuyy/blog/*.sql | mysql newdatabase
mysqlimport newdatabase /home/jpuyy/blog/*.txt

只导出表结构

mysql -hhostname -uusername -ppassword -d databasename > backup.sql

简单的每小时备份脚本

#/bin/bash
datetime=`date +%F-%H:%M`
mysqldump -udba -p'passwd' --opt --all-databases > all-databases-$datetime.sql
gzip all-databases-$datetime.sql

2012 年 4 月 13 日

Category: MySQL

mysql绑定多个ip地址

mysql主从复制

mysql 中的 show命令

mysql数据库空间用满时，巧用软链接转移

另一种解决方案：不用软链接而直接将数据库转移到目录/mnt/sx_disk

加大mysql的max_allowed_packet值

mysql备份和还原数据库