mysql 啟動停止腳本 and mysql 遷移 導入和導出

####監控腳本

[root@pdb~]# more /opt/VRTS/scripts/mysql_monitor.sh

#!/bin/sh

n=`ps -ef |grep mysql|grep "port=3306"|wc -l`

if test $n -eq 0

then exit 100

else exit 110

fi

[root@pdb~]# sh /opt/VRTS/scripts/mysql_monitor.sh

##############2 啟動

[root@pdb~]# cd /opt/VRTS/scripts/mysql_monitor.sh

[root@pdb~]# cd /opt/VRTS/scripts

[root@pdbscripts]# ls

mysql_monitor.sh start_mysql.sh stop_mysql.sh

[root@pdbscripts]# more start*.sh

base=/oa/oas_mysql/app/mysql/

datadir=/oa/oas_mysql/data/mydata

config_file=/oa/oas_mysql/app/mysql/my.cnf

user=mysql

/oa/oas_mysql/app/mysql/bin/mysqld_safe --defaults-file=${config_file} --datadir=${datadir} --user=$user &

#########3 停止

[root@pdbscripts]# more stop*.sh

/oa/oas_mysql/app/mysql/bin/mysqladmin -uroot -pmysql shutdown

#############sample mysql  sample 導入和導出

############mysql 導入導出

CREATE DATABASE `db_monitor` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

賦予資料庫（zqxt）權限給某使用者，可以是已經存在的使用者或新使用者名

GRANT ALL PRIVILEGES ON zqxt.* TO "任意使用者名"@"localhost" IDENTIFIED BY "新密碼";

重新整理權限

FLUSH PRIVILEGES;

退出資料庫shell

EXIT;

1.備份單個資料庫的資料和結構(,資料庫名mydb)

mysqldump -uroot-p123456 db_monitor>c:\db_monitor.sql

2.還原單個資料庫(需指定資料庫)

(1) mysql>use mydb

mysql>source f:\mydb.sql

(2) mysql -uroot -p123456 mydb <c:\db_monitor.sql

# 輸入密碼開始導入資料

#######command

https://dev.mysql.com/doc/refman/8.0/en/testing-server.html

If you specify a database name, mysqlshow displays a list of the tables within the database:

shell> bin/mysql -e "SELECT User, Host, plugin FROM mysql.user" mysql

shell> bin/mysqlshow mysql

Use mysqladmin to verify that the server is running. The following commands provide simple tests to check whether the server is up and responding to connections:

shell> bin/mysqladmin version

shell> bin/mysqladmin variables

###############phpadmin連接配接遠端資料庫

phpadmin連接配接遠端資料庫

1、打開wampserver下phpmyadmin的目錄在D:\wamp\apps\下 D:\wamp\apps\phpmyadmin4.1.14

2、找到config.inc.php檔案，打開修改要連接配接的遠端MySQL的host，user和password，更改這3個資訊即可

其中必須保證資料庫有被遠端連接配接的權限

/* Authentication type */

$cfg['Servers'][$i]['verbose'] = 'mysql wampserver';

//$cfg['Servers'][$i]['auth_type'] = 'cookie';

$cfg['Servers'][$i]['auth_type'] = 'config';

$cfg['Servers'][$i]['user'] = 'admin';

$cfg['Servers'][$i]['password'] = 'root';

/* Server parameters */

$cfg['Servers'][$i]['host'] = '192.168.1.154';

$cfg['Servers'][$i]['connect_type'] = 'tcp';

$cfg['Servers'][$i]['compress'] = false;

/* Select mysql if your server does not have mysqli */

$cfg['Servers'][$i]['extension'] = 'mysqli';

$cfg['Servers'][$i]['AllowNoPassword'] = true;

3.如果要用root登入此時也應該把host修改為localhost；

#######手工啟停mysql

##section 1

10.241.95.221 (master)

##loging

su - mysql

mysql -P 3306 -uroot -pmysql

##with a non-default port

###mysql -S /mysql/mysql/data/mysqltmp/mysql_3307.sock -uroot -pmysql

##show master status

show master\G;

###shutdown db (with user mysql password mysql)

mysqladmin -uroot -p shutdown -S /mysql/mysql/data/mysqltmp/mysql.sock

###start db (with root)

/mysql/mysql/app/mysql/bin/mysqld_safe --defaults-file=/mysql/mysql/app/mysql/my.cnf &

##section 2

10.241.95.222 (slave)

###show slave staus

##show slave status

show slave status\G;