MYSQL安装和主从配置
MYSQL安装和主从配置----------------------------------------
1.必备软件和编译安装
------------------------------
cd /soft
wget http://soft.local.com/mysql-5.6.17.tar.gz
yum -y install make gcc-c++ cmake bison-develncurses-devel
useradd mysql -s /sbin/nologin -M
tar zxf mysql-5.6.17.tar.gz
cd mysql-5.6.17
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql5.6.17 \
-DSYSCONFDIR=/usr/local/mysql5.6.17/etc \
-DMYSQL_DATADIR=/usr/local/mysql5.6.17/data \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_UNIX_ADDR=/usr/local/mysql5.6.17/tmp/mysqld.sock \
-DMYSQL_USER=mysql \
-DDEFAULT_CHARSET=gbk \
-DDEFAULT_COLLATION=gbk_chinese_ci \
-DEXTRA_CHARSETS=all \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1
make
make install
2.配置参数和初始化启动
-----------------------------------
chown -R mysql:mysql /usr/local/mysql5.6.17/data/
ln -s /usr/local/mysql5.6.17/ /usr/local/mysql
mkdir -p /usr/local/mysql/etc
cp /soft/mysql-5.6.17/support-files/my-default.cnf /usr/local/mysql/etc/my.cnf
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/ --user=mysql
mv /etc/my.cnf /etc/my.cnf.bak
mv /usr/local/mysql/my.cnf /usr/local/mysql/my.cnf.bak
cp /soft/mysql-5.6.17/support-files/mysql.server /etc/init.d/mysqld
chmod 700 /etc/init.d/mysqld
/etc/init.d/mysqld start
chkconfig --add mysqld
chkconfig --level 3 mysqld on
chkconfig --list | grep mysqld
echo "export PATH=$PATH:/usr/local/mysql/bin">>/etc/profile
source /etc/profile
/etc/init.d/mysqld start
mysqladmin -uroot password "123456"
mysql -uroot -p"123456" -e "select user,host from mysql.user;"
mysql -uroot -p"123456"
drop database test;
delete from mysql.user;
grant all privileges on *.* to sqladmin@'localhost'>
grant all privileges on *.* to sqladmin@127.0.0.1> flush privileges;
show grants for sqladmin@'localhost';
quit
mysql -usqladmin -p"123456" -e "select user,host from mysql.user;"
mysql -usqladmin -p"123456" -e "show databases;"
3.my.cnf文件配置
-----------------------------------
cat > /usr/local/mysql/etc/my.cnf > /etc/profile
source /etc/profile
echo $PATH
/bin/cp /soft/mysql-5.1.73/support-files/mysql.server /etc/init.d/mysqld
chmod 700 /etc/init.d/mysqld
/etc/init.d/mysqld start
chkconfig --add mysqld
chkconfig mysqld on
chkconfig --list mysqld
mysqladmin -uroot password "123456"
mysql -uroot -p"123456" -e "select user,host from mysql.user;"
mysql -uroot -p"123456"
drop database test;
delete from mysql.user;
grant all privileges on *.* to sqladmin@'localhost'>
grant all privileges on *.* to sqladmin@127.0.0.1> flush privileges;
show grants for sqladmin@'localhost';
quit
mysql -usqladmin -p"123456" -e "select user,host from mysql.user;"
mysql -usqladmin -p"123456"
quit
=====================================================
MySql主从同步配置
----------------------------------------------
1. 修改master服务器的配置文件
vim /etc/my.cnf
log-bin=mysql-bin
server-id = 1
#增加两行，server-id 的数字master，slave服务器不能相同
#log-bin一般放到数据库的data目录下
2. 重启mysql，查看sql的参数
/etc/init.d/mysqld restart
show variables like 'log_bin';
#在mysql配置文件中的参数，也都可以在mysql命令行中通过show variables查看
3.做slave从库授权同步账号
grant replication slave on *.* to rep@'192.168.1.%'> flush privileges;
select user,host from mysql.user;
4.锁表
flush tables with read lock;
#锁表，锁表时间受下面两个参数影响， interactive_timeout，wait_timeout
##锁表只在mysql命令行不退出的情况下生效，退出则失效。因此备份需要新开窗口进行备份
##因此写脚本时需要全部命令都在mysql>中运行，所以可能需要mysql>system 去执行linux的命令
##查看主数据库的状态，可以查看到mysql-bin的位置信息
show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000017 | 3575 | | |
+------------------+----------+--------------+------------------+
5. 备份master库
mysqldump -usqladmin -p'123456' --events -B -A| gzip > /tmp/bak.sql.gz
#-A表示备份所有库，-B表示备份时使用use 数据库.
ll /tmp/bak.sql.gzip
6.解锁数据库
unlock tables;
7.忽略mysql库的主从同步,可以指定需要同步的数据库或者忽略需要不要同步的数据库
vim /etc/my.cnf
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
以下需要在slave服务器上配置
-------------------------------------------
6.解压拷贝数据库
gzip -d /tmp/bak.sql.gz
#gzip解压时会删除原文件，因此需要先cp备份一下
scp yjb007@192.168.1.14:/tmp/bak.sql /tmp/
#scp拷贝，注意因为设置了ssh的root不能登陆，因此无法使用root用户拷贝，这里使用的yjb007账号拷贝
7.在slave上还原数据库
mysql -usqladmin -p'123456'< /tmp/bak.sql
#还原数据库会将所有的同名数据库覆盖，mysql的默认管理员root的密码也会被重置
8.配置slave服务器的参数
vim /etc/my.cnf
#log-bin=mysql-bin
server-id = 5
#log-bin一般不启用，除非此slave继续做别的slave的master，或者将此slave做备份服务器，
#注意log-bin=mysql-bin最好每个服务器都配置成一致的，避免以后出错
#log-bin是存放数据库更新的sql语句，数据库完整并备份必须有全备和bin-log才是完整的备份
重启mysql，查看sql的参数
/etc/init.d/mysqld restart
show variables like 'log_bin';
9.配置同步参数，需要在linux的命令行中配置
cat | mysql -usqladmin -p'123456' /etc/init.d/mysqld stop
rm -rf /usr/local/mysql/data/*relay-bin.*
rm -rf /usr/local/mysql/data/relay-log.info
rm -rf /usr/local/mysql/data/master.info
/etc/init.d/mysqld start
mysql -usqladmin -p'123456' -e 'reset master;'
mysql -usqladmin -p'123456' -e 'stop slave;'
mysql -usqladmin -p'123456' -e 'stop slave io_thread;'
mysql -usqladmin -p'123456' -e 'start slave;'
mysql -usqladmin -p'123456' -e 'reset master'
mysql -usqladmin -p'123456' -e 'show slave status\G' | egrep 'Seconds_Behind_Master|Slave_IO_Running|Slave_SQL_Running'
mysql -usqladmin -p'123456' -e 'show master status;'
14.从库升级主库后其他从库跟新主同步流程
mysql -usqladmin -p'123456' -e 'stop slave;'
cat |mysql -usqladmin -p'123456'slow.2.dat
#按照平均query time排序查看日志
4. 其他参数
-r 倒着排序
-a 不要将数字和字符串抽象成N和S
-g 仅仅分析相匹配的query,后边可以写一个正则匹配模式,大小写不敏感.
-t 限制只显示多少行，top
5. 常用命令
由上面的常用参数就可以组合出如下的常用命令：
mysqldumpslow -s t slow.log.old > slow.1.dat
#按照query time排序查看日志
mysqldumpslow -s at slow.log.old > slow.2.dat
#按照平均query time排序查看日志
mysqldumpslow -a -s at slow.log.old > slow.3.dat
#按照平均query time排序并且不抽象数字的方式排序
mysqldumpslow -a -s c slow.log.old > slow.4.dat
#安装执行次数排序
mysqldumpslow -s c -t 10 host-slow.log
#列出返回记录集最多的10个sql语句
mysqldumpslow -s c -t 10-r host-slow.log
#列出返回记录集最多的10个sql语句,倒着排序
mysqldumpslow -s t -t 10 -g "left join" host-slow.log
#按照时间返回前10条里面含有左连接的sql语句
一些错误处理方法
-----------------------
Plugin 'InnoDB' registration as a STORAGE ENGINE failed
#未正常关闭mysql的情况下,修改my.cnf的innodb相关参数,可能会出现如上错误在mysql日志中
#解决方式是修改之后删除ib_logfile文件
#ib_logfile文件中记录些innodb引擎非常有用的信息比如说默认的innodb默认的配置信息
#在未正常关闭关闭mysql的情况下，可能导致重启后的server不支持innodb引擎。
rm -rf ib_logfile*
#重启mysql生效
-----------------------------
InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists
Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists
InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists
Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.
InnoDB: Cannot open table mysql/innodb_index_stats from the internal data dictionary of InnoDB though the .frm file for the table exists
InnoDB: Error: table `mysql`.`innodb_index_stats` does not exist in the InnoDB internal
#问题产生原因：具体原因目前不详，网上查找到的资料:数据库打开这几张表的默认引擎为MyISAM，但是这几张表在建表时的引擎为INNODB
#但是能确定的，这几张表确实是在mysql5.6中新入的
innodb_index_stats,
innodb_tables_stats,
slave_master_info,
slave_relay_log_info,
slave_worker_info
解决方法：
(1) 登录数据库，进入mysql库，执行如下SQL删除5张表
记住，一定要是drop table if exists
use mysql;
drop table if exists innodb_index_stats;
drop table if exists innodb_table_stats;
drop table if exists slave_master_info;
drop table if exists slave_relay_log_info;
drop table if exists slave_worker_info;
如下是执行的结果，忽略你看到的Warning信息
admin@localhost : mysql 02:12:26> drop table if exists innodb_index_stats;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Warning (Code 155): Table 'mysql.innodb_index_stats' doesn't exist
admin@localhost : mysql 02:12:26> drop table if exists innodb_table_stats;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Warning (Code 155): Table 'mysql.innodb_table_stats' doesn't exist
admin@localhost : mysql 02:12:26> drop table if exists slave_master_info;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Warning (Code 155): Table 'mysql.slave_master_info' doesn't exist
admin@localhost : mysql 02:12:27> drop table if exists slave_relay_log_info;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Warning (Code 155): Table 'mysql.slave_relay_log_info' doesn't exist
admin@localhost : mysql 02:12:27> drop table if exists slave_worker_info;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Warning (Code 155): Table 'mysql.slave_worker_info' doesn't exist
执行完后，可以用show tables查看一下，看表的数据是否已经比删除之前减少了，如果减少了，说明你成功了！
这一部操作完成后，停止数据库，并进入到数据库数据文件所在目录，删除表面5个表所对应的idb文件，如下所示：
ll /usr/local/mysql/data/mysql/*.ibd
innodb_index_stats.ibd
innodb_table_stats.ibd
slave_master_info.ibd
slave_relay_log_info.ibd
slave_worker_info.ibd
rm -f /usr/local/mysql/data/mysql/*.ibd
重新启动数据库，进入到mysql库，重建上面被删除的表结构：
数据库的建设表脚本在/usr/local/mysql5.6.17/share/mysql_system_tables.sql
/etc/init.d/mysqld restart
mysql -usqladmin -p'123456' -e "use mysql;source /usr/local/mysql5.6.17/share/mysql_system_tables.sql;"
mysql -usqladmin -p'123456' -e "use mysql;show tables;"
用show create table命令查看是否创建了之前的5张表
后台日志中也不再报与上面提到的5张表相关的错误
到此，问题全部解决！
----------------------------------------------
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.
登录数据库后，删除5张表，并重新导入脚本
use mysql
drop tableslave_master_info;
drop tableslave_relay_log_info;
drop tableslave_worker_info;
drop tableinnodb_index_stats;
drop tableinnodb_table_stats;
source /usr/coolpad/mysql/share/mysql_system_tables.sql
重新启动数据库
这一步是必须的，否则无法正常配置双主架构。
一些mysql常用的命令
---------------------------------------------------------------
create database test;
#create database test DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
#create database test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
create user yjb007@'192.168.1.%'> ##创建用户，先创建再授权
show grants for 'yjb007'@'192.168.1.%';
##查看用户的权限
show grants for current_user;
show grants for current_user();
#查看当前用户的权限
grant all privileges on test1.* to yjb007@'192.168.1.%';
##授权一个已经创建的用户，先创建再授权
grant all privileges on test2.* to yjb009@'192.168.1.%'> select user,host from mysql.user;
mysql -uyjb007 -p'123456' -h 192.168.1.13
##链接到另外一台主机的数据库,需要注意远程用户有相应权限
grant all privileges on test1.* to yjb007@'192.168.1.%';
#授予所有权限
revoke create on test1.* from yjb007@'192.168.1.%';
#撤回create权限
all privileges 权限包包含GRANT SELECT, INSERT, UPDATE, DELETE, DROP, REFERENCES, INDEX,>
grant insert,delete,select,update on test2.* to yjb006@'192.168.1.%'> ##生产环境建议一般只授予 insert,delete,select,update增删查改4个只对表内容进行操作的选项
create database test_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
show databases like '%gbk%';
#%是通配符，不能用*代替
use test_gbk;
create table test (
`id` int(4) not null primary key auto_increment,
`name` char(20) not null,
key `index_name`(`name`)
);
##创建表，设立主键，建立索引，key是建立索引的意思，index_name是索引的名称，可随便起
#主键列默认是已经建立索引的
show index from test; #查看表的索引
show index from test\G #查看表的索引
alter table test drop index index_name; #删除索引index_name
alter table test add index index_name (`name`);#创建索引，alter可以修改表结构
create index index_name2 on test_gbk.test (`name`); #创建索引，但不能创建主键索引
show tables from test_gbk;
show create table tab_test_gbk\G
#查看创建 表的情况
desc mysql.user;
desctab_test_gbk;
show columns from mysql.user;
show columns from tab_test_gbk;
#都是查看表的结构，columns是列的意思
##insert,delete,select,update只操作表的内容
##create,drop可以创建删除数据库和表
##alter可以操作表的结构，如index，primary key,增加删除列，修改列名称
alter table test add sex111 char(4) comment '这里是填写说明的地方';
alter table test add `测试` char(6) ; #列名注意用反单引号
alter table test add age char(4) after name;
alter table test add age1 char(4) first;
alter table test drop sex;
alter table test change>
alter table test modify> insert into test (id,name) values(002,'测试') ;
insert into test (name) values('wangwu') ;
insert into test (name) values('masan') ,('masan2'),('masan3');#同时插入多行
insert into testvalues (100,'masan5') ,(101,'masan6'),(102,'masan6'); #同时插入多行
insert into test (name) values ('测试');
update test set name='yujianbo' where test.name='wangwu'; #修改某行的列的值，不指定行则修改整列，危险
delete from test where name='yujianbo'; #删除特定行
select count(*) from test; #查看有多少行
select * from test limit 2; #查看前两行
select * from test limit 2,8; #查看2-8行
select * from test order by>
select * from test where>
select * from test where>
select * from test where>
select * from test where>
select test1.id,test3.chengji from test1,test2,test3 where test1.id=test2.id and test2.chengji=test3.chengji order by> #多表联合查询
explain select * from test where name='masan5';
#explain可以查看sql语句的执行情况，查看key的值可以查看此sql语句是否使用了索引
truncate table test; #清空某个表的内容
drop table test1;#删除表
rename table test1 to test2;#修改表的名称
alter table test2 rename to test1; #修改表的名称
mysql>system pwd
mysql>system vim test.sql;
mysql>source test.sql; #source可以执行sql语句，在做数据库恢复时可以用
关于数据库乱码的问题
--------------------------------
1.安装数据库时指定字符集，例如gbk,utf8,
2.创建数据库时指定字符集，
3.mysql5.1版本默然创建数据库是拉丁字符集，mysql5.5版本默认安装的跟安装mysql5.5时指定参数相同字符集
4.create database test DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
5.set names gbk;#可以调整字符集，然后再插入数据就可以正常中文了
show character set; #查看mysql支持的字符集和校对规则,gbk是GBK Simplified Chinese
show variables; #查看mysql内置的变量
show variables like '%character%'; #字符集的变量
+--------------------------+----------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
set names gbk; #修改了客户端和返回结果的字符集
show variables like '%character%';
+--------------------------+----------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
或者直接在/etc/my.cnf中修改
default-character-set=gbk #可以解决中文乱码及中文插入的问题
#这个参数可能不在有效5.1版本
default-character-set=gbk
character-set-server=gbk #5.5版本
数据库备份
-------------------------
mysqldump -uroot -p'123456' -B test > /tmp/$(date +%F).sql
mysql -uroot -p'123456'< /tmp/2014-07-06.sql
##恢复时可以不加恢复的数据库，因为-B的存在
#备份数据库，实质上备份的数据库里面的表,-B备份会在备份里存在自动创建数据库的命令
mysql -uroot -p'123456' test_new < /tmp/2014-07-06.sql
#恢复数据库也是恢复数据库里的表，可以将表恢复到别的数据库里面
mysqldump -uroot -p'123456' -B test|gzip > /tmp/2014-07-06.sql.gz
#压缩备份
mysqldump -uroot -p'123456' -B --default-character-set=gbk test|gzip > /tmp/2014-07-06.sql.gz
#指定字符集备份,备份时需要带这个参数
mysqldump -uroot -p'123456' -B test test > /tmp/$(date +%F).sql
##备份某个表，不用-B，因为数据库还是存在的
mysqldump -uroot -p'123456' -d test1 > /tmp/d$(date +%F).sql
#备份一个数据库的结构，不包含数据
mysqldump -uroot -p'123456' -d test1 test> /tmp/d$(date +%F).sql
#备份一个表的结构，不包含数据
show processlist; #查看正在执行的sql语句，用来查看执行效率低的语句，优化时用，重要
show full processlist;
mysql -uroot -p'123456' -e 'show full processlist;' | grep -v Sleep#生产环境中常用
show global status; #可以查看到一些命令的执行次数，增删查改等等等等
MYSQL参数优化
vim /etc/my.cnf
-----------------------------
#物理内存的50%，重要参数
#更新后立即刷新log
Mysql语句优化
----------------------------------------
创建索引，查看索引,删除索引
create index index_num on student(Num);
show create table student\G
drop index index_num on student;
create index index_a_b_c on student(a,b(10),c(20));
#创建联合索引，a列，b列前10个字符，c列前20个字符
explain select * from table where snum=6\G
explain select SQL_NO_CACHE * from table where snum=6\G
#查看查询是否走了索引，可以使用explain,查看key键值，如果为NULL则说明没有使用索引
#使用show full processlist;查看慢查询语句，然后使用explain来查看该语句是否走索引
#SQL_NO_CACHE不使用缓存，建议这么操作
select count(*)from user;
#查看有多少行
select count(distinct user )from user;
#查看user列去重复后有多少行
数据库负载高问题排除
1.show full processlist；查看卡住的语句，查看共性
2.使用explain，查看语句是否有索引
3.找出需要做索引的列，使用select count(distinct user )查询哪列的去重复数量
4，找相关人员，研发，确定在哪列建索引，因为语句是研发写的
5.大表建索引需要耗费时间，一定要和领导商量好时间
#查询时间超过1s的语句都会记录到这个慢查询的位置，每天只要查看这个文件就可以了，非常重要
#还有一个可以将不使用索引的语句也写入到这个文件的参数
#定时分析这个文件，然后邮件通知
#拆表时，假如根据时间拆分，1年的表，两年前的表，查询时需要携带时间条件，然后根据时间hash转发到对应##的表去查询
##-F 起到类似MSSQL的截断日志的功能，会从全备后开始生产新的日志文件
##延迟备份的好处在于有人为出错时可以有效恢复数据
备份日志：
使用这个命令可以人为的按时间进行日志切割，这样恢复时可以恢复到某个时间点
Mysql分库备份
Mysql分表备份
数据库恢复步骤
将日志文件通过mysqlbinlog命令转换成sql语句，并清除掉人为出错的命令，如dropdatabase test;
-d oldboy指定只摘出oldboy库中的sql语句，因为现实中的日志中会存在多个数据库的插入日志语句
mysql
##恢复日志文件时注意需要在命令中指定要恢复的库，不然恢复不过来
提升从库为主库步骤：
页:
[1]