1.lamp架构下

[root@server1 redis]# yum install nginx-1.8.0-1.el6.ngx.x86_64.rpm -y [root@server1 redis]# yum install php-* -y [root@server1 redis]# /etc/init.d/php-fpm start [root@server1 redis]# cd /etc/nginx/ [root@server1 nginx]# vim nginx.conf user apache; worker_processes 1; [root@server1 nginx]# cd conf.d/ [root@server1 conf.d]# vim default.conf ****************************************** location / { root /usr/share/nginx/html; index index.php index.html index.htm; } location ~ \.php$ { root /usr/share/nginx/html; fastcgi_pass 127.0.0.1:9000; fastcgi_index index.php; fastcgi_param SCRIPT_FILENAME /usr/share/nginx/html/$fastcgi_script_name; include fastcgi_params; } ****************************************** [root@server1 conf.d]# /etc/init.d/nginx start [root@server1 conf.d]# cd /usr/share/nginx/html/ [root@server1 html]# vim index.php <?php phpinfo() ?> [root@server1 html]# vim /etc/php.ini [Date] ; Defines the default timezone used by the date functions ; http://www.php.net/manual/en/datetime.configuration.php#ini.date.timezone date.timezone = Asia/Shanghai [root@server1 html]# /etc/init.d/php-fpm reload [root@server1 redis]# unzip phpredis-master.zip [root@server1 redis]# cd phpredis-master [root@server1 phpredis-master]# phpize [root@server1 phpredis-master]# yum install gcc* -y [root@server1 phpredis-master]# ./configure --enable-redis [root@server1 phpredis-master]# make [root@server1 phpredis-master]# make install [root@server1 phpredis-master]# cd /usr/lib64/php/modules/ [root@server1 modules]# cd /etc/php.d/ [root@server1 php.d]# cp mysql.ini redis.ini [root@server1 php.d]# vim redis.ini extension=redis.so [root@server1 php.d]# php -m | grep redis redis [root@server1 php.d]# /etc/init.d/php-fpm reload [root@server2 ~]# yum install mysql-server -y [root@server1 redis]# scp redis-4.0.8.tar.gz server2: [root@server1 redis]# scp redis-4.0.8.tar.gz server3:

2.redis安装（server1，server2同样安装）

[root@server3 ~]# tar zxf redis-4.0.8.tar.gz [root@server3 ~]# cd redis-4.0.8 [root@server3 redis-4.0.8]# make [root@server3 redis-4.0.8]# make install [root@server3 redis-4.0.8]# cd utils/ [root@server3 utils]# ./install_server.sh ##自动安装配置 [root@server3 utils]# cd /etc/redis/ [root@server3 redis]# vim 6379.conf bind 0.0.0.0 [root@server3 redis]# /etc/init.d/redis_6379 restart [root@server3 redis]# redis-cli ##基础操作 127.0.0.1:6379> set name westos OK 127.0.0.1:6379> get name "westos" 127.0.0.1:6379> del name (integer) 1 127.0.0.1:6379> get name (nil) 127.0.0.1:6379> CONFIG GET * ##显示所有指令 93) "slave-announce-port" 94) "0" 95) "min-slaves-to-write" 96) "0" 97) "min-slaves-max-lag" 98) "10" 127.0.0.1:6379> CONFIG set min-slaves-to-write 1 ## [root@server1 redis]# sysctl -a | grep vm.over vm.overcommit_memory = 0 ## vm.overcommit_ratio = 50 [root@server1 redis]# sysctl vm.overcommit_memory vm.overcommit_memory = 0 [root@server1 redis]# sysctl -w vm.overcommit_memory=1 | 2 [root@server1 redis]# vim 6379.conf bind 0.0.0.0 ##监听全局 slaveof 172.25.18.3 6379 ##从属于172.25.18.3 6379 [root@server2 redis]# vim 6379.conf bind 0.0.0.0 ##监听全局 slaveof 172.25.18.3 6379 ##从属于172.25.18.3 6379 测试： server3上set数据 server1，server2上同步显示

3.

[root@server3 redis-4.0.8]# cp sentinel.conf /etc/redis/ [root@server3 redis]# vim sentinel.conf protected-mode no ##关闭保护 sentinel monitor mymaster 172.25.18.2 6379 2 ##设置主master sentinel down-after-milliseconds mymaster 5000 ##时间5s sentinel failover-timeout mymaster 5000 ##时间5s [root@server3 redis]# scp sentinel.conf server1:/etc/redis/ [root@server3 redis]# scp sentinel.conf server2:/etc/redis/ [root@server1 redis]# redis-sentinal /etc/redis/sentinel.conf ##一定要在分发完毕后再启动 [root@server2 redis]# redis-sentinal /etc/redis/sentinel.conf [root@server3 redis]# redis-sentinal /etc/redis/sentinel.conf 此时三台主机都同步缓存了，server3为master，server1，server2为slave 重新连接一个server3 [root@server3 ~]# redis-cli 127.0.0.1:6379> info ##查看具体参数 127.0.0.1:6379> shutdown ##关闭服务进程 经info查看，竞选server1为master server3需手动启动，并添加slaveof指向server1 [root@server3 ~]# vim /etc/redis/6379.conf slaveof 172.25.18.1 6379 [root@server3 ~]# /etc/init.d/redis_6379 restart server1,server2全部关闭redis，并取消开机自起 [root@server3 redis]# vim /etc/redis/6379.conf ****************************************************************************** #slaveof 172.25.18.1 6379 [root@server1 redis]# vim test.php <?php $redis = new Redis(); $redis->connect('172.25.18.3',6379) or die ("could net connect redis server"); # $query = "select * from test limit 9"; $query = "select * from test"; for ($key = 1; $key < 10; $key++) { if (!$redis->get($key)) { $connect = mysql_connect('172.25.18.2','redis','westos'); mysql_select_db(test); $result = mysql_query($query); //如果没有找到$key,就将该查询sql的结果缓存到redis while ($row = mysql_fetch_assoc($result)) { $redis->set($row['id'],$row['name']); } $myserver = 'mysql'; break; } else { $myserver = "redis"; $data[$key] = $redis->get($key); } } echo $myserver; echo "<br>"; for ($key = 1; $key < 10; $key++) { echo "number is <b><font color=#FF0000>$key</font></b>"; echo "<br>"; echo "name is <b><font color=#FF0000>$data[$key]</font></b>"; echo "<br>"; } ?> ****************************************************************************** [root@server1 redis]# cp test.php /usr/share/nginx/html/ [root@server2 ~]# mysql mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ mysql> grant all on test.* to redis@'%' identified by 'westos'; [root@server1 redis]# scp test.sql server2:~ [root@server2 ~]# mysql < test.sql client -> r/w redis -> mysql client -> gearmand -> worker [root@server1 redis]# yum install gearmand-1.1.8-2.el6.x86_64.rpm libgearman-1.1.8-2.el6.x86_64.rpm libgearman-devel-1.1.8-2.el6.x86_64.rpm libevent-devel-1.4.13-4.el6.x86_64.rpm libevent-* -y [root@server1 redis]# /etc/init.d/gearmand start [root@server1 redis]# scp gearman-mysql-udf-0.6.tar.gz lib_mysqludf_json-master.zip server2: [root@server2 ~]# yum install mysql-devel -y [root@server2 ~]# unzip lib_mysqludf_json-master.zip [root@server2 ~]# cd lib_mysqludf_json-master [root@server2 lib_mysqludf_json-master]# gcc $(mysql_config --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c [root@server2 lib_mysqludf_json-master]# cp lib_mysqludf_json.so /usr/lib64/mysql/plugin/ [root@server2 lib_mysqludf_json-master]# cat lib_mysqludf_json.sql [root@server2 lib_mysqludf_json-master]# mysql mysql> create function json_object returns string soname 'lib_mysqludf_json.so'; mysql> select * from mysql.func; +-------------+-----+----------------------+----------+ | name | ret | dl | type | +-------------+-----+----------------------+----------+ | json_object | 0 | lib_mysqludf_json.so | function | +-------------+-----+----------------------+----------+ [root@server1 redis]# scp libgearman-* libevent-* server2: [root@server2 ~]# yum install -y libgearman-* libevent-* [root@server2 ~]# tar zxf gearman-mysql-udf-0.6.tar.gz [root@server2 ~]# cd gearman-mysql-udf-0.6 [root@server2 gearman-mysql-udf-0.6]# ./configure --with-mysql --libdir=/usr/lib64/mysql/plugin/ [root@server2 gearman-mysql-udf-0.6]# make && make install [root@server2 gearman-mysql-udf-0.6]# cd /usr/lib64/mysql/plugin/ [root@server2 plugin]# ll libgearman_mysql_udf.so lrwxrwxrwx 1 root root 29 Apr 25 15:38 libgearman_mysql_udf.so -> libgearman_mysql_udf.so.0.0.0 mysql> CREATE FUNCTION gman_do_background RETURNS STRING SONAME 'libgearman_mysql_udf.so'; mysql> CREATE FUNCTION gman_servers_set RETURNS STRING SONAME 'libgearman_mysql_udf.so'; mysql> select * from mysql.func; +--------------------+-----+-------------------------+----------+ | name | ret | dl | type | +--------------------+-----+-------------------------+----------+ | json_object | 0 | lib_mysqludf_json.so | function | | gman_do_background | 0 | libgearman_mysql_udf.so | function | | gman_servers_set | 0 | libgearman_mysql_udf.so | function | +--------------------+-----+-------------------------+----------+ mysql> SELECT gman_servers_set('172.25.18.1:4730'); +--------------------------------------+ | gman_servers_set('172.25.18.1:4730') | +--------------------------------------+ | 172.25.18.1:4730 | +--------------------------------------+ [root@server2 ~]# vim test.sql ************************************************************ use test; #CREATE TABLE `test` (`id` int(7) NOT NULL AUTO_INCREMENT, `name` char(8) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; #INSERT INTO `test` VALUES (1,'test1'),(2,'test2'),(3,'test3'),(4,'test4'),(5,'test5'),(6,'test6'),(7,'test7'),(8,'test8'),(9,'test9'); DELIMITER $$ CREATE TRIGGER datatoredis AFTER UPDATE ON test FOR EACH ROW BEGIN SET @RECV=gman_do_background('syncToRedis', json_object(NEW.id as `id`, NEW.name as `name`)); END$$ DELIMITER ; ************************************************************ [root@server2 ~]# mysql < test.sql [root@server1 redis]# tar zxf gearman-1.1.2.tgz [root@server1 redis]# cd gearman-1.1.2 [root@server1 gearman-1.1.2]# phpize [root@server1 gearman-1.1.2]# ./configure [root@server1 gearman-1.1.2]# make && make install [root@server1 gearman-1.1.2]# cd /etc/php.d/ [root@server1 php.d]# cp redis.ini gearman.ini [root@server1 php.d]# vim gearman.ini extension=gearman.so [root@server1 php.d]# /etc/init.d/php-fpm reload [root@server1 php.d]# php -m | grep gearman [root@server1 redis]# cp worker.php /usr/local/ [root@server1 redis]# cd /usr/local/ [root@server1 local]# vim worker.php ************************************************ <?php $worker = new GearmanWorker(); $worker->addServer(); $worker->addFunction('syncToRedis', 'syncToRedis'); $redis = new Redis(); $redis->connect('172.25.18.3', 6379); while($worker->work()); function syncToRedis($job) { global $redis; $workString = $job->workload(); $work = json_decode($workString); if(!isset($work->id)){ return false; } $redis->set($work->id, $work->name); } ?> ************************************************ [root@server1 local]# nohup php worker.php & 测试： 浏览器上访问 http://172.25.18.1/test.php 在数据库里更新，redis端同样更新 mysql> update test set name='devin' where id='1'; 模型： client : mysql -> update -> trigger -> gearman-utf | -> json-utf | | gearmand : server:4730 | | | worker : php -> redis sync: redis:6379 -> gearman

一些mysql的操作

mysql> show variables like '%slow%'; ##慢缓存 +---------------------------+---------------------------------+ | Variable_name | Value | +---------------------------+---------------------------------+ | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/server1-slow.log | +---------------------------+---------------------------------+ mysql> set global slow_query_log=1; ##开启慢缓存 Query OK, 0 rows affected (0.00 sec) mysql> select sleep(10); ##休眠10s，测试慢缓存 +-----------+ | sleep(10) | +-----------+ | 0 | +-----------+ mysql> show variables like '%query%'; +------------------------------+---------------------------------+ | Variable_name | Value | +------------------------------+---------------------------------+ | binlog_rows_query_log_events | OFF | | ft_query_expansion_limit | 20 | | have_query_cache | YES | | long_query_time | 10.000000 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/server1-slow.log | +------------------------------+---------------------------------+ mysql> show status like '%slow%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | Slow_launch_threads | 0 | | Slow_queries | 1 | +---------------------+-------+ mysql> show variables like '%indexes%'; +----------------------------------------+-------+ | Variable_name | Value | +----------------------------------------+-------+ | log_queries_not_using_indexes | OFF | | log_throttle_queries_not_using_indexes | 0 | +----------------------------------------+-------+ mysql> show variables like '%binlog%'; +-----------------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | ABORT_SERVER | | binlog_format | ROW | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_gtid_simple_recovery | ON | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | innodb_api_enable_binlog | OFF | | innodb_locks_unsafe_for_binlog | OFF | | log_statements_unsafe_for_binlog | ON | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | sync_binlog | 1 | +-----------------------------------------+----------------------+

mysql的bin_log的三种模式

Statement: 优点：记录的简单，内容少 缺点：导致主从不一致 例： 100w条记录 updatetest set name=’tintin’; binilog 里面就只用update test set name=’tintin’;

ROW 行级模式 优点：记录数据详细（每行），主从一直 缺点：占用大量的磁盘空间，降低了磁盘的性能 100w条记录 updatetest set name=’tintin’; binilog 里面就用100w条update test set name=’tintin’;语句

MIXED混合模式 100w条记录 updatetest set name=’tintin’; binilog 里面就只用update test set name=’tintin’; 对于函数，触发器，存储过程 会自动的使用row-level模式

RBR 的优点：

任何情况都可以被复制，这对复制来说是最安全可靠的 和其他大多数数据库系统的复制技术一样 多数情况下，从服务器上的表如果有主键的话，复制就会快了很多 复制以下几种语句时的行锁更少： * INSERT ... SELECT * 包含 AUTO_INCREMENT 字段的 INSERT * 没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句 执行 INSERT，UPDATE，DELETE 语句时锁更少 从服务器上采用多线程来执行复制成为可能

RBR 的缺点：

binlog 大了很多 复杂的回滚时 binlog 中会包含大量的数据 主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写问题 UDF 产生的大 BLOB 值会导致复制变慢 无法从 binlog 中看到都复制了写什么语句 当在非事务表上执行一段堆积的SQL语句时，最好采用 SBR 模式，否则很容易导致主从服务器的数据不一致情况发生 mysql> show variables like '