数据库重启命令行_mysql数据库操作

作者&＃xff1a;是烫的不是自来卷

一&＃xff1a;数据库(mysql)

1 备份命令

# mysqldump -uroot -pDbb790827253&＃43; -h192.168.0.180 --all-databases --single_transaction --flush-log --master-data&＃61;2 --lock-tables > /data/mysqlbackup.sql

导入

#mysql -uzabbix -p123456 -h172.20.100.3 zabbix

2 创建库和用户命令创建库

mysql> create database zabbix character set utf8 collate utf8_bin;

授权用户

mysql> grant all privileges on zabbix.* to zabbix&＃64;"%" identified by &＃39;123456&＃39;;

3 主从命令

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO &＃39;rsync-user&＃39;&＃64;&＃39;172.20.100.%&＃39; IDENTIFIED BY &＃39;123456&＃39;;mysql> CHANGE MASTER TO MASTER_HOST&＃61;&＃39;172.20.100.3&＃39;,MASTER_USER&＃61;&＃39;rsyncuser&＃39;,MASTER_PASSWORD&＃61;&＃39;123456&＃39;,MASTER_LOG_FILE&＃61;&＃39;bin-log.xxxxxx&＃39;,MASTER_LOG_POS&＃61;xxx;

4 查询语句

mysql> show variables like &＃39;%connect%&＃39;;

mysql> show variables like &＃39;%max_connections%&＃39;;

mysql> desc notice_config;

mysql> show master logs;

查看连接数

mysql> show processlist;

mysql> show processlist G;

5 查询mysql的日志问题

# memory

6查看用户和密码 修改密码查看到用户和密码

MySQL [(none)]> select host,user,password from mysql.user;

如果要修改密码的话&＃xff0c;在命令行下执行下面的语句

update mysql.user set password&＃61;&＃39;这里填写你要设置的密码&＃39; where user&＃61;&＃39;root&＃39;;

update user set password&＃61;password(&＃39;123456&＃39;) where user&＃61;&＃39;root&＃39; and host&＃61;&＃39;localhost&＃39;;

7查看数据库的配置在里面

mysql> show variables like &＃39;pxc_strict_mode%&＃39;;

8 修改数据库密码

安装完数据库第一次设置密码方式 输完命令按回车&＃xff0c;不用在下面输入。

#mysqladmin -u root -p password 123456

Enter password:

其设置密码有三种方法:

a. ./mysqladmin -u root -p oldpassword newpasswd(记住这个命令是在/usr/local/mysql/bin中外部命令)

b. SET PASSWORD FOR root&＃61;PASSWORD(’new password’);(对登录数据库后这种方式)

c. UPDATE user SET password&＃61;PASSWORD(”new password”) WHERE user&＃61;’root’; 　(对登录数据库后这种方式)

生效

mysql> flush privileges;

数据库sql语句

查询数据库表的大小

SELECT table_name AS "Tables",

round(((data_length &＃43; index_length) / 1024 / 1024), 2) "Size in MB"

FROM information_schema.TABLES

WHERE table_schema &＃61; &＃39;zabbix&＃39;

ORDER BY (data_length &＃43; index_length) DESC;

9 增加root远程权限

GRANT ALL PRIVILEGES ON *.* TO &＃39;root&＃39;&＃64;&＃39;%&＃39; IDENTIFIED BY &＃39;123456&＃39;&＃xff1b;

flush privileges;

10&＃xff1a;#skip-grant-tables 无视密码登录

11&＃xff1a;安装mariadb数据库

#yum -y install mariadb mariadb-server

#systemctl start mariadb

#systemctl enable mariadb

接下来进行MariaDB的相关简单配置&＃xff0c;设置密码&＃xff0c;会提示先输入密码

[root&＃64;test-vm03 ~]# mysql_secure_installation

首先是设置密码&＃xff0c;会提示先输入密码

Enter current password for root (enter for none):

设置密码

Set root password? [Y/n]

New password:

Re-enter new password:

其他配置

Remove anonymous users? [Y/n]

Disallow root login remotely? [Y/n]

Remove test database and access to it? [Y/n]

Reload privilege tables now? [Y/n]

12 配置MariaDB的字符集

接下来配置MariaDB的字符集:

-> 首先是配置文件/etc/my.cnf&＃xff0c;在[mysqld]标签下添加

init_connect&＃61;&＃39;SET collation_connection &＃61; utf8_unicode_ci&＃39;

init_connect&＃61;&＃39;SET NAMES utf8&＃39;

character-set-server&＃61;utf8

collation-server&＃61;utf8_unicode_ci

skip-character-set-client-handshake

-> 接着配置文件/etc/my.cnf.d/client.cnf&＃xff0c;在[client]中添加

default-character-set&＃61;utf8

-> 然后配置文件/etc/my.cnf.d/mysql-clients.cnf&＃xff0c;在[mysql]中添加

default-character-set&＃61;utf8

13创库和授权

mysql数据库

create database zabbix default character set utf8 collate utf8_bin;

grant all on zabbix.* to &＃39;zabbix&＃39;&＃64;&＃39;%&＃39; identified by &＃39;123456&＃39;;

flush privileges;

14&＃xff1a;修改最大连接数

MYSQL修改最大连接数

方法一&＃xff1a;

set GLOBAL max_connections&＃61;512;

这种方法在重启服务后会失效

方法二&＃xff1a;

修改mysql配置文件my.cnf&＃xff0c;在[mysqld]段中添加或修改max_connections值&＃xff1a;

max_connections&＃61;512

MariaDB修改最大连接数

1.mariadb数据库最大连接数&＃xff0c;默认为151

MariaDB [(none)]> show variables like &＃39;max_connections&＃39;;

&＃43;-----------------&＃43;-------&＃43;

| Variable_name | Value |

&＃43;-----------------&＃43;-------&＃43;

| max_connections | 151 |

&＃43;-----------------&＃43;-------&＃43;

2.配置/etc/my.cnf

[mysqld]下新添加一行如下参数&＃xff1a;

max_connections&＃61;3000

systemctl restart mariadb 重启mariadb服务&＃xff0c;再次查看mariadb数据库最大连接数&＃xff0c;最大连接数是214&＃xff0c;并非我们设置的3000。

MariaDB [(none)]> show variables like &＃39;max_connections&＃39;;

&＃43;-----------------&＃43;-------&＃43;

| Variable_name | Value |

&＃43;-----------------&＃43;-------&＃43;

| max_connections | 214 |

&＃43;-----------------&＃43;-------&＃43;

由于mariadb有默认打开文件数限制

vi /usr/lib/systemd/system/mariadb.service

取消[Service]前的#号&＃xff0c;

[Service]新添加两行如下参数&＃xff1a;

LimitNOFILE&＃61;10000

LimitNPROC&＃61;10000

4.重新加载系统服务&＃xff0c;并重启mariadb服务

systemctl --system daemon-reload

systemctl restart mariadb.service

再次查看mariadb数据库最大连接数&＃xff0c;可以看到最大连接数已经是3000

MariaDB [(none)]> show variables like &＃39;max_connections&＃39;;