1、下载
下载地址:http://dev.mysql.com/downloads/mysql/5.6.html#downloads
下载版本:我这里选择的5.6.33,通用版,linux下64位
也可以直接复制64位的下载地址,通过命令下载:wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz
yum install gcc-c++
yum install pcre-devel
yum install zlib-devel
yum install openssl-devel;
yum install git
yum install wget
yum install tree
yum install perl-devel
yum install -y perl-Module-Install.noarch
yum install -y mysql-server mysql mysql-deve
yum install -y libaio
2、解压
1
2
3
4
tar
-zxvf mysql-5.6.33-linux-glibc2.5-x86_64.
tar
.gz
cp
-r mysql-5.6.33-linux-glibc2.5-x86_64
/usr/local/mysql
3、添加用户组和用户
1
2
3
4
groupadd mysql
useradd
-g mysql mysql
4、安装
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
cd
/usr/local/mysql/
<br>
mkdir
.
/data/mysql
chown
-R mysql:mysql ./
.
/scripts/mysql_install_db
--user=mysql --datadir=
/usr/local/mysql/data/mysql
cp
support-files
/mysql
.server
/etc/init
.d
/mysqld
chmod
755
/etc/init
.d
/mysqld
cp
support-files
/my-default
.cnf
/etc/my
.cnf
vi
/etc/init
.d
/mysqld
basedir=
/usr/local/mysql/
datadir=
/usr/local/mysql/data/mysql
service mysqld start
.
/mysql/bin/mysql
-uroot
export
PATH=$PATH:
/usr/local/mysql//bin
<br>
source
/etc/profile
service mysqld start
service mysqld stop
service mysqld status
5、错误
5.1 sqlyog连接时,报1130错误,是由于没有给远程连接的用户权限问题
解决1:更改 ‘mysql’数据库‘user’表‘host’项,从‘localhost’改成‘%’。
use mysql;select 'host' from user where user='root'; update user set host = '%' where user ='root';flush privileges;
解决2:直接授权
GRANT ALL PRIVILEGES ON *.* TO ‘root’@'%’ IDENTIFIED BY ‘youpassword’ WITH GRANT OPTION;
5.2 安装时的一些错误
-bash: ./scripts/mysql_install_db: /usr/bin/perl: bad interpreter: 没有那个文件或目录
解决: yum -y install perl perl-devel
Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
解决:yum -y install libaio-devel
6、其他
6.1 配置环境变量
vi + /etc/profile
export PATH=....:/usr/local/mysql/bin
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
default-character-set=utf8
5.mysql指令:
5.1 客户端访问:
mysql -u root -p 打开mysql客户端 默认打开主机为 localhost
在初次开启mysql服务后:
# mysqladmin -u root password '123'; //设置初始密码 root@localhost
mysqladmin -u root password 'new-password'
show databases;//查看所有的数据库
use mysql ;//选择mysql库
select user,host,password from user;//查看user表中的用户信息
为用户指定密码
update user set password = password('xxxx') where xxxxx
mysql默认不允许远程访问
将user表中host改为 : '%'
update user set host='%' where xxx
flush privileges;//刷新权限
再次登录mysql时:
mysql -u root -h 192.168.0.xxx -p
mysql所在主机IP
====================================================================================================================
====================================================================================================================
java中使用mysql:
1>导入驱动jar:mysql-connector-java-x-bin.jar
2>为数据源指定mysql的相关参数
url=jdbc:mysql://192.168.0.203:3306/c74?useUnicode=true&characterEncoding=UTF-8
driver=com.mysql.jdbc.Driver
username=root
password=456
====================================================================================================================
中文乱码解决:
建库时指定utf-8
url中指定utf-8
====================================================================================================================
找到 /etc/my.cnf 在此文件中添加如下
[client]
default-character-set=utf8
防止命令行乱码,此配置仅作用于用命令行操作数据时的乱码解决。
====================================================================================================================
11.mysql卸载
rpm -e mysql-server
rm -rf /var/lib/mysql
重装即可
此时只需要安装:rpm -ivh mysql-server.... 即可
====================================================================================================================
1.安装两个mysql数据库 分别预设为主机和从机
service mysqld start
mysql -u root -p
show databases;
create database mydb;
use mydb;
create table user
(
ID int primary key auto_increment,
NAME VARCHAR(100),
PASSWORD VARCHAR(100)
);ENGINE=InnoDB DEFAULT CHARSET=UTF8;
insert into USER (name,password) values ("lxj","666");
2.配置主机和从机
2.1主机配置: /etc/my.cnf
在[mysqld]下配置:
server-id=8 //id
log-bin=master-bin //二进制日志文件的名称
binlog-do-db=mydb //要记录二进制日志的数据库,可以有多个,如下
#binlog-do-db=mydb1
#binlog-do-db=mydb2
expire_logs_days=10 //日志过期时间
#binlog_format=MIXED
#binlog-ignore-db=xx //不记录二进制日志的数据库
2.2 从机配置:/etc/my.cnf
在[mysqld]下:
server-id=10
replicate-do-db=mydb
master-connect-retry=60 和主机断开连接后,会在60秒后重连
#replicate-ignore-db=mydb1
#replicate-ignore-db=mydb2
2.3 同步主从的初始数据状态:
1>从主机导出数据库db9中的数据:从机执行的
[root@zhj ~]# mysqldump -u root -h 192.168.0.124 -p mydb > /root/mydb.sql (之后输入密码即可)
2>在从机中导入数据
*在从机中创建数据库:mydb
*导入数据:登录mysql,并选择数据库mydb,然后执行如下:
mysql> source /root/mydb.sql
2.4 重启主机:service mysqld restart
2.5 查看主机状态:
mysql -u root -p
mysql>show master status;
*记录下file字段值,和position字段值,要告知给从机
2.6 从机备份设置:
重启从机:service mysqld restart
登录从机,并执行:
mysql -u root -p
1>slave stop; #停止slave同步进程 reset slave;
2>change master to master_host='192.168.0.124',
master_user='root',
master_password='123',
master_log_file='master-bin.000001',
master_log_pos=106;
(注意:此处的master_log_pos一定要和"主mysql"的positon一致
在主机中登录,通过"show master status"查看主机的position
)
3>slave start; #开启slave同步进程
4>SHOW SLAVE STATUS\G #查看slave同步信息(G后不要加分号)
5>注意查看:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
以上这两个参数的值为Yes,即说明配置成功!