mysql操作记录

mysqldump spms_service -uspms -pspms | mysql spms_dev -uspms -pspms　快速复制数据库

 

use mysql;
select host,user,password from user;

grant all privileges on *.* to root@'%' identified by "root" with grant option;
grant all privileges on *.* to root@'localhost' identified by "adminhongxin" with grant option;
grant all privileges on *.* to root@'127.0.0.1' identified by "food@hongxincd2013" with grant option;
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication';

flush privileges;
show grants;

#skip-grant-tables＝1

#update mysql.user user set authentication_string = password('Test@123'), password_expired = 'N', password_last_changed = now() where user = 'root';#mysql 5.7更改密码

mysql -h172.168.17.67 -ufood -pfood@hongxincd2013 -e "show databases;"
mysql -h172.168.17.67 -ufood -pfood@hongxincd2013 -e "flush tables with read lock;"
mysql -h172.168.17.67 -ufood -pfood@hongxincd2013 -e "show master status;"
mysqldump -h172.168.17.67 -ufood -pfood@hongxincd2013 -e food > /opt/food-`date "+%Y-%m-%d"`.sql
mysql -h172.168.17.67 -ufood -pfood@hongxincd2013 -e "unlock tables;"

mysqldump -uroot  -proot --all-databases --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 > changhong.sql

more mei.sql #查看log files and post site

stop slave;
mysql -uroot -proot < changhong.sql
3306

CHANGE MASTER TO MASTER_HOST='192.168.2.3',MASTER_PORT=3306,MASTER_USER='replication', MASTER_PASSWORD='replication',MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=154;

start slave

show slave status\G;

===================================================================================================

[mysqld]
port = 3306
socket = /data/mysql/mysql.sock
datadir = /data/mysql/

lower_case_table_names=1

server-id = 2
log-bin=mysql-bin
binlog_format=mixed
expire_logs_days = 10

skip-external-locking
skip-name-resolve
#skip-networking #本地调用开启，远程tcp调用关闭

key_buffer_size = 16M
max_connections = 1000
max_allowed_packet = 16M

[mysqld_safe]

pid-file=/var/run/mysqld/mysqld.pid

log_error = /data/mysql/log/mysql-error.log
slow_query_log = 1
slow_query_log_file = /data/mysql/log/mysql-slow.log

===================================================================================================