mysql的配置&备份&主从

1--mysql的下载地址以及安装

--dev.mysql.com下载 Community 社区 Generic一般

-- http://cdn.mysql.com/archives/mysql-5.1/mysql-5.1.40-linux-i686-icc-glibc23.tar.gz

2--查看mysql文件大小，安装包默认位置，解压

--du -sh 查看大小

--ls /usr/local/src 安装包默认存放路径

--tar -zxvf 解压

3--创建mysql用户

--useradd -s /sbin/nologin -M mysql 创建mysql用户，不创建家目录

--ls /home/

4--将解压后的mysql移动并重命名

--mv mysql-5.1.40-linux-i686-icc-glibc23 /usr/local/mysql移动加重命名

--cd /usr/local/mysql 进入mysql下

--mkdir -p /data/mysql 创建数据存储目录

--chown -R mysql /data/mysql 更改权限

5--mysql初始化

-- ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql mysql初始化

-- 初始化的过程中出现两个OK为正常 echo $?也可以

6--更换mysql配置文件的位置

--cd /usr/local/mysql/support-files/ 配置文件

--cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf复制

--cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld 把启动服务拷贝到/etc/init.d下

7--编辑mysql配置文件

--/etc/init.d/mysqld 此处的mysqld指的是“cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld 把启动服务拷贝到/etc/init.d下” 更改basedir datadir

basedir=/usr/local/mysql

datadir=/data/mysql

8--添加到启动项

--chkconfig --add mysqld添加到启动项中

--chkconfig mysqld on

--/etc/init.d/mysqld start 启动

--ps aux | grep mysql 显示进程 netstat -lnp | grep mysql 查看端口，查看启动

9--mysql调优

MySQL调优可以从几个方面来做：

1. 架构层：

做从库，实现读写分离；

2.系统层次：

增加内存；

给磁盘做raid0或者raid5以增加磁盘的读写速度；

可以重新挂载磁盘，并加上noatime参数，这样可以减少磁盘的i/o;

3. MySQL本身调优：

(1) 如果未配置主从同步，可以把bin-log功能关闭，减少磁盘i/o

(2) 在my.cnf中加上skip-name-resolve,这样可以避免由于解析主机名延迟造成mysql执行慢

(3) 调整几个关键的buffer和cache。调整的依据，主要根据数据库的状态来调试。如何调优可以参考 第5

4. 应用层次：

查看慢查询日志，根据慢查询日志优化程序中的SQL语句，比如增加索引

5. 调整几个关键的buffer和cache

1) key_buffer_size  首先可以根据系统的内存大小设定它，大概的一个参考值：1G以下内存设定128M；2G/256M; 4G/384M;8G/1024M；16G/2048M.这个值可以通过检查状态值Key_read_requests和 Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。注意：该参数值设置的过大反而会是服务器整体效率降低!

2) table_open_cache 打开一个表的时候，会临时把表里面的数据放到这部分内存中，一般设置成1024就够了，它的大小我们可以通过这样的方法来衡量： 如果你发现 open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

3) sort_buffer_size 查询排序时所能使用的缓冲区大小,该参数对应的分配内存是每连接独占!如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 4 = 400MB。所以，对于内存在4GB左右的服务器推荐设置为4-8M。

4) read_buffer_size 读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享!

5) join_buffer_size 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享!

6) myisam_sort_buffer_size 这个缓冲区主要用于修复表过程中排序索引使用的内存或者是建立索引时排序索引用到的内存大小，一般4G内存给64M即可。

7) query_cache_size MySQL查询操作缓冲区的大小，通过以下做法调整：SHOW STATUS LIKE ‘Qcache%’; 如果Qcache_lowmem_prunes该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。如果该值非常大，则表明经常出现缓冲不够的情况，需要增加缓存大小;Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，我们可以根据实际情况做出调整。一般情况下4G内存设置64M足够了。

8) thread_cache_size 表示可以重新利用保存在缓存中线程的数，参考如下值：1G  —> 8 2G  —> 16 3G  —> 32  >3G  —> 64

除此之外，还有几个比较关键的参数：

9) thread_concurrency 这个值设置为cpu核数的2倍即可

10) wait_timeout 表示空闲的连接超时时间，默认是28800s，这个参数是和interactive_timeout一起使用的，也就是说要想让wait_timeout 生效，必须同时设置interactive_timeout，建议他们两个都设置为10

11) max_connect_errors 是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。与性能并无太大关系。为了避免一些错误我们一般都设置比较大，比如说10000 

12) max_connections 最大的连接数，根据业务请求量适当调整，设置500足够

13) max_user_connections 是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。通常我们设置为100足够 

10--mysql配置讲解

-- vim /etc/my.cnf mysql的配置位置

--[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

interactive_timeout = 8 一定时间自动断开

wait_time = 8

long_query_time = 1记录慢查询

log_slow_queries = /data/mysql/slow.log

--mysql -uroot 进入mysql

--show variables; mysql运行的参数

--SHOW STATUS LIKE 'key_read%'；

11-- mysql的root密码重置

-- mysqladmin -uroot password 'wangerzheng' 设置密码

-- mysql -uroot -pwangerzheng 用密码登录

-- skip-grant vim /etc/my.cnf添加 直接在/etc/my.cnf添加即可，登录mysql跳过密码验证

--/etc/init.d/mysqld restart 重启

--mysql 可以直接进来 use mysql 进入数据库后还要进入相应的库才能操作update;

--update user set password=password('wanger') where user='root';

--select * from user where user='root'\G;

12--mysql远程登录

-- mysql -uroot -h192.168.1.132 -P3306 -pwanger 远程登录

-- telnet 192.168.1.132 3306 查看是否允许

-- mysql -uroot -h127.0.0.1 -P3306 -pwanger 默认已经授权远程登录

-- grant all on *.* to 'root'@'192.168.1.132' identified by '123456';此授权是在192.168.1.104授权

让192.168.1.132可以访问

-- use mysql select * from user where host='192.168.1.132'\G;查看

-- select use(); 查看当前用户

--mysql -uroot -S /tmp/mysql.sock -p 本地有多个mysql,可以这样登录

13--mysql常用操作

-- show databases; 查看有哪些库

-- use mysql use discuz 切换库

--select database(); 查看在哪个数据库下

--select user(); 查看用户

-- select version(); 查看版本信息

--库--表--行--字段

--show tables; 查看表

--desc pre_ucenter_vars; desc加表名查看行

--show create table pre_ucenter_vars \G;查看表的创建语句

--create database wang; 创建一个库

-- create table tb1 (`id` int(4), `name` char(40)) ENGINE=MyISAM DEFAULT CHARSET=gbk; 创建一个表

--insert into tb1 values(1,'wang');插入一条数据

-- select * from tb1; 查看

--insert into tb1 (`id`) values(3); 单独插入

-- update tb1 set id=3 where name = 'er';更改一条数据

--delete from tb1 where name= 'zheng'; 删除一行

-- truncate table wang.tb1; 清空

--drop table tb1; 删除一个表

-- drop database wang; 删除一个库；

--grant all on discuz.* to 'user1'@'%' identified by 'ajflasjlgjlsdf'; 授权 %代替所有IP或者192.168.1.%；

--flush privileges; 刷新权限

-- show processlist; 查看数据库有什么队列

--show variables;查看变量

-- set global max_connections = 200; 设置参数

--show variables like 'max_con%'; 单独查看

--show status; 查看状态 show status like '%running';

--vim /etc/init.d/mysqld cd /data/mysql wangerzheng.err错误日志

--repair table pre_ucenter_settings; 修一个表

14--mysql备份和恢复

-- mysqldump -uroot -pwanger discuz 备份

-- mysqldump -uroot -pwanger discuz > /data/discuz.sql 备份文件存放至/data/discuz.sql

-- cd /data/mysql/ 数据文件位置

-- rm -rf pre_forum_post* 测试恢复

--mysql -uroot -pwanger discuz < /data/discuz.sql 恢复数据

-- mysqldump -uroot -pxxxxx --events --ignore-table=mysql.events --all-databases > all.sql mysql默认是不备份事件表的，只有加了events才能解决

-- mysqldump -uroot -pwanger discuz pre_forum_post > /data/post.sql 备份一个表

-- mysql -uroot -pwanger discuz < /data/post.sql恢复

-- mysqldump -uroot --default-character-set=gbk -pwanger discuz pre_forum_post > /data/post.sql 指定字符utf8

--mysql -uroot --default-character-set=gbk -pwanger discuz < /data/post.sql 指定字符

15--mysql主从配置-1

--replication 复制

--A --> change data --> bin_log --> transfer 转移 --> B --> repl_log -->change data

--rm -rf /usr/local/mysql

--cd /usr/local/src

-- tar zxvf mysql-5.1.40-linux-i686-icc-glibc23.tar.gz

-- mv mysql-5.1.40-linux-i686-icc-glibc23 /usr/local/mysql

--ls /usr/local/mysql/

--grep 'mysql' /etc/passwd 检查有没有mysql用户，没有的需要创建

--useradd -s /sbin/nologin mysql

--cd /usr/local/mysql/

-- cp support-files/my-small.cnf /etc/my.cnf

-- cp support-files/mysql.server /etc/init.d/mysqld

-- vim /etc/init.d/mysqld 编辑此文件

basedir=/usr/local/mysql

datadir=/data/mysql

--rm -rf /data/mysql

--./scripts/mysql_install_db --user=mysql --datadir=/data/mysql 出现两个ok

--/etc/init.d/mysqld start 二次安装的可能会报错，杀掉原来的进程

--ps aux | grep mysql ps -ef | grep mysql kill -9 2901 删除进程

--netstat -lnp | grep 3306

--cd /usr/local/

--cp -r mysql mysql_slave 安装第二个mysql

--cd mysql_slave

--cp /etc/my.cnf .点 此处试讲my.cnf放置/usr/local/mysql_slave中

--vim my.cnf 编辑/usr/local/mysql_slave/my.cnf

--[mysqld]

27 port = 3307

28 socket = /tmp/mysql_slave.sock

29 datadir = /data/mysql_slave

--./scripts/mysql_install_db --user=mysql --datadir=/data/mysql_slave

-- ls /data/mysql_slave 检查下面是否有两个目录 mysql test

--cd /etc/init.d/ cp mysqld mysqldslave

--vim mysqlslave 编辑

basedir=/usr/local/mysql

datadir=/data/mysql

conf=$basedir/my.cnf

--/etc/init.d/mysqldslave start

-- netstat -lnp | grep mysql

--ps aux | grep mysql

16--主从配置-2

--which mysql

-- mysql -S /tmp/mysql.sock 一个机器上两个mysql如何登陆

-- mysql -h127.0.0.1 -P3306

--create database db1;

--mysqldump -S /tmp/mysql.sock mysql > 123.sql

--mysql -S /tmp/mysql.sock db1 < 123.sql

--use db1; show tables;

-- vim /etc/my.cnf 编辑

server-id = 1

#Uncomment the following if you want to log updates

log-bin=wang

binlog-ignore-db=mysql 不去同步的 binlog-do-db=db1 去同步的

--/etc/init.d/mysqld restart ls /data/mysql 会看见 wang.0000001文件

--主 mysql grant replication slave on *.* to 'repl'@'127.0.0.1' identified by '123456';

--flush privileges; flush tables with read lock; show master status;

--从 vim /usr/local/mysql_slave/my.cnf server-id = 111

--mysql -S /tmp/mysql_slave.sock -e "create database db1"

--mysql -S /tmp/mysql_slave.sock db1 < 123.sql

--登陆从 slave stop; 主从关闭同步

-- change master to master_host='127.0.0.1', master_port=3306, master_user='repl', master_password='123456', master_log_file='wang.000001', master_log_pos=106;

--slave start; 启动了从

--show slave status\G; 查看是否配置成功

Slave_IO_Running: No

Slave_SQL_Running: Yes 两个都是yes表示成功

--更改配置文件需要重启服务；

17--mysql主从配置-3

--主解锁 unlock tables;

-- use db1; show tables; drop table he;

--从 use db1; show tables; 查看是否删掉；

--show create table user\G;

--drop database db1; show database;

--从上不要写数据