分类目录归档：Database

mysql批量导入导出

备份数据库到脚本（20分钟）

mysqldump -u username -p password --all-databases --flush-privileges | gzip  > all.sql.gz

拷贝到新机器（20分钟）

scp -P xxx -r all.sql.gz root@host:~

解压缩(2分钟)

gunzip  all.sql.gz

解决掉视图权限问题（1分钟）

sed '/\/*!50013/d' all.sql  > all_without_50013.sql

导入（40分钟）

mysql -u username -p password < all_without_50013.sql

mysql导入视图错误ERROR 1449

MYSQL导入时，如果存在视图，可能导致导入不成功，错误提示： ERROR 1449(HY000) at line xxx: The user specified as a definer (‘xxx’@’xxx’) does not exist

解决办法1：

sed '/\/*!50013/d' backup.sql > backup_without_50013.sql

解决办法2：

$contents = file_get_contents('backups/backup.sql');
$contents = preg_replace('@/*!50013 DEFINER.*?*/@' , '' , $contents);
file_put_contents('backups/backup.sql' , $contents);

Import mysql dump files with view definer and sql security

使用left join替代not exists查询

使用 NOT EXISTS

SELECT  table1.column1,  table1.column2
      FROM table1
          WHERE NOT 
              EXISTS (
                    SELECT table2.column1, table2.column2
                    FROM table2
                    WHERE table2.column1= table1.column1
                    AND table2.location_id= 6
                )
      GROUP BY Barcode

使用 LEFT JOIN

SELECT  table1.column1,  table1.column2
   FROM table1
   LEFT JOIN table2
   ON table2.column1= table1.column1
     AND table2.location_id= 6
   WHERE table2.column1 is null
   GROUP By Barcode

mysql配置优化

SHOW VARIABLES LIKE ‘%cache%’;
+——————————+———————-+
| Variable_name | Value |
+——————————+———————-+
| binlog_cache_size | 32768 |
| binlog_stmt_cache_size | 32768 |
| have_flashcache | YES |
| have_query_cache | YES |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| metadata_locks_cache_size | 1024 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| stored_program_cache | 256 |
| table_definition_cache | 400 |
| table_open_cache | 400 |
| thread_cache_size | 8 |
+——————————+———————-+

show status like ‘%cache%’;
+——————————–+———-+
| Variable_name | Value |
+——————————–+———-+
| Binlog_cache_disk_use | 2 |
| Binlog_cache_use | 15514049 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 1170 |
| Com_assign_to_keycache | 0 |
| Flashcache_enabled | OFF |
| Qcache_free_blocks | 2005 |
| Qcache_free_memory | 6565712 |
| Qcache_hits | 77732 |
| Qcache_inserts | 590516 |
| Qcache_lowmem_prunes | 557510 |
| Qcache_not_cached | 338374 |
| Qcache_queries_in_cache | 7191 |
| Qcache_total_blocks | 16631 |
| Ssl_callback_cache_hits | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_used_session_cache_entries | 0 |
| Threads_cached | 3 |
+——————————–+———-+

mysql缓存参数
 MYSQL缓冲区和缓存设置
 MySQL 数据库性能优化之缓存参数优化
 MySQL写入优化

使用vagrant快速搭建MariaDB Galera Cluster集群测试环境

参考

下载并安装ubuntu 12.04的32位box 初始化vagrant box

wget http://files.vagrantup.com/precise32.box
vagrant box add base Downloads/precise32.box
vagrant init

设置最大内存512

vim ~/VagrantFile

config.vm.provider :virtualbox do |vb|
    # Don't boot with headless mode
    vb.gui = false

    # Use VBoxManage to customize the VM. For example to change memory:
    vb.customize ["modifyvm", :id, "--memory", "512"]
end

配置虚拟IP

config.vm.network :private_network, ip: "10.0.1.100"

启动虚拟机修改为163源

启动虚拟机，并通过ssh登录到客户机

vagrant up
vagrant ssh

采用163.com的源

sudo vim /etc/apt/sources.list

deb http://mirrors.163.com/ubuntu/ precise main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ precise-security main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ precise-updates main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ precise-proposed main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ precise-backports main restricted universe multiverse
deb-src http://mirrors.163.com/ubuntu/ precise main restricted universe multiverse
deb-src http://mirrors.163.com/ubuntu/ precise-security main restricted universe multiverse
deb-src http://mirrors.163.com/ubuntu/ precise-updates main restricted universe multiverse
deb-src http://mirrors.163.com/ubuntu/ precise-proposed main restricted universe multiverse
deb-src http://mirrors.163.com/ubuntu/ precise-backports main restricted universe multiverse

deb http://archive.ubuntu.com/ubuntu/ precise main restricted universe multiverse
deb http://archive.ubuntu.com/ubuntu/ precise-security main restricted universe multiverse
deb http://archive.ubuntu.com/ubuntu/ precise-updates main restricted universe multiverse
deb http://archive.ubuntu.com/ubuntu/ precise-proposed main restricted universe multiverse
deb http://archive.ubuntu.com/ubuntu/ precise-backports main restricted universe multiverse
deb-src http://archive.ubuntu.com/ubuntu/ precise main restricted universe multiverse
deb-src http://archive.ubuntu.com/ubuntu/ precise-security main restricted universe multiverse
deb-src http://archive.ubuntu.com/ubuntu/ precise-updates main restricted universe multiverse
deb-src http://archive.ubuntu.com/ubuntu/ precise-proposed main restricted universe multiverse
deb-src http://archive.ubuntu.com/ubuntu/ precise-backports main restricted universe multiverse

更新源

sudo aptitude update

添加mariadb源，并安装集群版

大部分linux的二进制发行包都可以在这里找到：https://downloads.mariadb.org/mariadb/repositories/

apt-get install python-software-properties
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
sudo add-apt-repository &#039;deb http://mirrors.tuna.tsinghua.edu.cn/mariadb/repo/5.5/ubuntu precise main&#039;
sudo aptitude update
sudo aptitude install mariadb-galera-server mariadb-client

打包虚拟机，以便再次安装退出客户机，回到主机界面

vargrant package --output galera-db.box
vargant box add db galera-db.box

配置虚拟机

vim ~/Vagrantfile

Vagrant.configure("2") do |config|
  config.vm.provision "shell", inline: "echo Hello"
  config.vm.define "db1" do |db1|
    db1.vm.box = "db"
    db1.vm.network :private_network, ip: "10.0.1.100"
  end

  config.vm.define "db2" do |db2|
    db2.vm.box = "db"
    db2.vm.network :private_network, ip: "10.0.1.101"
  end
end

重新加载虚拟机配置

vagrant reload

配置第一台db节点

vagrant ssh db1
sudo service mysql start
sudo mysql_secure_installation mysql -uroot -p

mysql> GRANT USAGE ON *.* to sst@'%' IDENTIFIED BY 'sstpass123';
mysql> GRANT ALL PRIVILEGES on *.* to sst@'%';
mysql> FLUSH PRIVILEGES;
mysql> quit

sudo cp /usr/share/mysql/wsrep.cnf /etc/mysql/conf.d/ sudo vim /etc/mysql/conf.d/wsrep.cnf

wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://"
wsrep_sst_auth=sst:sstpass123
wsrep_sst_method=rsync
wsrep_node_name=db1
wsrep_node_address=10.0.1.100

sudo service mysql restart

安装第二个节点

vagrant ssh db2

除了wsrep.cnf有少许不同外，其他同第一节点

wsrep_cluster_address="gcomm://10.0.1.100"
wsrep_node_name=db2
wsrep_node_address=10.0.1.101

sudo service mysql restart

测试节点同步

mysql -usst -psstpass123 -h10.0.1.100 show status like 'wsrep%'; 可以看到wsrep_connected都为ON

create database testdb;
use testdb;
create table t(id int primary key auto_increment);
insert into t values(null);
insert into t values(null);
select * from t;
exit;

mysql -usst -psstpass123 -h10.0.1.101

use testdb;
select * from t;
insert into t values(null);
insert into t values(null);
select * from t;

参考：

mysql集群与负载均衡

MySQL数据库集群与负载均衡(LVS)
MySQL Cluster集群探索与实践
 高性能、高可用、可扩展的MySQL集群如何组建？
利用keepalived构建高可用MySQL-HA
详解MySQL集群下的复制(replicate)原理
 MySql集群搭建笔记
 MySQL集群简介与配置详解

为自己写代码

c4ys = Code for yourself

分类目录归档：Database

mysql批量导入导出

mysql导入视图错误ERROR 1449

使用left join替代not exists查询

使用 NOT EXISTS

使用 LEFT JOIN

mysql配置优化

使用vagrant快速搭建MariaDB Galera Cluster集群测试环境

下载并安装ubuntu 12.04的32位box 初始化vagrant box

设置最大内存512

配置虚拟IP

启动虚拟机修改为163源

启动虚拟机，并通过ssh登录到客户机

采用163.com的源

更新源

添加mariadb源，并安装集群版

打包虚拟机，以便再次安装退出客户机，回到主机界面

配置虚拟机

重新加载虚拟机配置

配置第一台db节点

安装第二个节点

测试节点同步

mysql集群与负载均衡

使用 NOT EXISTS

使用 LEFT JOIN

下载并安装ubuntu 12.04的32位box 初始化vagrant box

设置最大内存512

配置虚拟IP

启动虚拟机修改为163源

启动虚拟机，并通过ssh登录到客户机

采用163.com的源

更新源

添加mariadb源，并安装集群版

打包虚拟机，以便再次安装 退出客户机，回到主机界面

配置虚拟机

重新加载虚拟机配置

配置第一台db节点

安装第二个节点

测试节点同步

打包虚拟机，以便再次安装退出客户机，回到主机界面