mysql安装
- cmd,已管理员身份打开,路径:C:\Windows\System32;
- 进入mysql解压目录;
- 复制一份my-default.ini为my.ini的配置文件,配置端口字符编码;
- cmd进行服务注册:mysqld -install [服务名]
mysqld install MySQL_sync_1 –defaults-file=”C:\my_develop\dev_sdk\mysql\mysql-5.6.20-winx64\my.ini”
mysqld install MySQL_sync_2 –defaults-file=”C:\my_develop\dev_sdk\mysql\mysql22-5.6.20-winx64\my.ini”
5.cmd登录:mysql -u root -p; - 设置root密码:mysqladmin -u root password ‘123456’;
- 查看数据库表信息:a、show databases; user xxx;show tables;describe xxx;
提示:
- 未安装,登录:mysql -u root -p,出现提示:[登录成功]
关闭其他mysql服务,其他目标服务;
- 删除服务:sc delete [服务名]
- my.ini配置
[mysqld]
# These are commonly set, remove the # and set as required.
basedir = "C:\my_develop\dev_sdk\mysql\mysql-5.6.20-winx64"
datadir = "C:\my_develop\dev_sdk\mysql\mysql-5.6.20-winx64\data"
port = 3307
server_id = mysql_sync_1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#服务端的编码方式
character-set-server=utf8
[client]
#客户端编码方式,最好和服务端保存一致
loose-default-character-set = utf8
mysql基本命令
- 查看基本信息:#show variables like ‘%version%’;(查看版本信息)
- 查看字符编码:#status;(查看编码信息)
- 权限:
1:查看用户权限:#show grants [for “xx”@”%”];
2:创建同步用户:#GRANT REPLICATION SLAVE,RELOAD ON . TO ‘xxx’@’%’ IDENTIFIED BY ‘xxx’ ;
3:#flush privileges; - 查看错误日志:data/*.err
- 查看用户使用情况:#show processlist;
- 查看字符编码:show variables like “%char%”;
主从服务器
- 主服务配置:/etc/my.conf
binlog-do-db=test #提供数据同步服务的数据库 - 主、查看状态: #show master status;
- 从服务配置:
server_id = 2
replicate-do-db=xxx_backup #同步的数据库 - 从服务登录,再执行同步命令:
#change master to master_host=’masterIP’, master_user=’slave’, master_password=’slvaePASS’;
#slave start;
#stop slave;
#show slave status;
#show master status;
#show slave status;
#show processlist; - 从、查看状态:show slave status;
问题:
- ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
原因:全局权限(REPLICATION SLAVE、file、super)不能指定给数据库级别;
- unknown variable ‘master-host=xxx’
原因:Mysql版本从5.1.7以后开始就不支持“master-host”类似的参数
解决方式:执行从步命名:change master to master_host=’masterIP’, master_user=’slave’, master_password=’slvaePASS’;slave start;