linux 安装mysql 以及常见错误总结

最新推荐文章于 2024-09-22 06:11:21 发布

一只努力的景

最新推荐文章于 2024-09-22 06:11:21 发布

阅读量1.1k

点赞数

分类专栏： mysql

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

安装的方法菜鸟教程上有：入口

mysqld --initialize
#命令是出现如下错误Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

这段内容是转自一位博主的（关于上面这个问题的一个解释）：

在虚拟机中安装了CentOS时安装了Nami版的Rendmine，没有设置MySQL自动启动，手动启动时出现 Please read "Security" section of the manual to find out how to run mysqld as root!错误，根据提示，查了 /opt/redmine-1.2.1-1/mysql/docs/mysql.info的Security部分，发现是因为MySQL为了安全，不希望root用户直接启动mysql。下面是文档中的原文：

*Never run the MySQL server as the Unix `root' user. This is
  extremely dangerous, because any user with the `FILE' privilege is
  able to cause the server to create files as `root' (for example,
  `~root/.bashrc'). To prevent this, *Note `mysqld': mysqld. refuses
  to run as `root' unless that is specified explicitly using the
  `--user=root' option.

永远不要使用root帐号启动MySQL Server。这样做很微笑，因为拥有FILE'权限的用户会使得MySQL Server使用root帐户创建文件（比如，~root/.bashrc），为了防止类似的事情发生，mysqld默认拒绝用户使用root帐户启动，但root用户可以通过在命令后面加上"--user=root"选项来强行启动mysqld。

  *Note `mysqld': mysqld. can (and should) be run as an ordinary,
  unprivileged user instead. You can create a separate Unix account
  named `mysql' to make everything even more secure. Use this
  account only for administering MySQL. To start *Note `mysqld':
  mysqld. as a different Unix user, add a `user' option that
  specifies the user name in the `[mysqld]' group of the `my.cnf'
  option file where you specify server options. For example:

[mysqld]
user=mysql

与使用root用户启动mysqld相比，更好的方法是使用一个普通的、没有高级权限的用户帐户允许mysqld，例如创建一个名为mysql的用户帐户来专门管理MySQL。使用其帐启

动MySQL的方法是在mysqld命令后面加上一个用户选项，这个用户属于mysqld用户组并且位于my.cnf配置文件中。例如在创建mysql帐户后，可以将下面的内容添加到my.cnf文

件中：

[mysqld]
user=mysql

  This causes the server to start as the designated user whether you
  start it manually or by using *Note `mysqld_safe': mysqld-safe. or
  *Note `mysql.server': mysql-server. For more details, see *Note
  changing-mysql-user::.

这个选项可以使你使用指定的用户帐户启动MySQL，无论是mysqld_safe还是mysql.server命令，都可以使用。
  Running *Note `mysqld': mysqld. as a Unix user other than `root'
  does not mean that you need to change the `root' user name in the
  `user' table. _User names for MySQL accounts have nothing to do
  with user names for Unix accounts_.

不要使用Unix的root用户启动并不意味着你要修改MySQL中的user表中的root用户名，因为Unix的root帐户和MySQL的root帐户没有什么关系。
原文：https://blog.csdn.net/jubincn/article/details/6725582

whereis mysql

#回车，如果你安装好了mysql，就会显示文件安装的地址，例如我的显示(安装地址可能会不同)

[root@localhost ~]# whereis mysql 
mysql: /usr/bin/mysql /usr/lib/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz


二、查询运行文件所在路径(文件夹地址)

如果你只要查询文件的运行文件所在地址，直接用下面的命令就可以了(还是以mysql为例)：

which mysql

终端显示:

[root@localhost ~]# which mysql 
/usr/bin/mysql

下面的这个内容是百度上提供的一个思路

引用自另一篇文章：http://www.cnblogs.com/qiusuo/p/9451717.html

数据库初始化

默认情况下，数据已经初始化好，数据可参见默认配置文件/etc/my.cnf

在其他位置重新初始化MySQL数据库：

basedir是mysql的安装根目录，ldata是数据初始化的目录

mysql_install_db --basedir=/ --ldata=./data

相关提示：

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

  mysqladmin -u root password 'new-password'
  mysqladmin -u root -h gzns-map-tc-spd-server-shanghai00.gzns.baidu.com password 'new-password'

Alternatively you can run:

  mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

  cd . ; mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

  cd mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

WARNING: Found existing config file /etc/my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /etc/my-new.cnf,
please compare it with your file and take the changes you need.

WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server

数据库实例配置

my.cnf配置文件内容

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

log-error=/<your_dir>/log/mysql.log.err
general_log = ON
general_log_file=/<your_dir>/log/mysql_general.log
slow_query_log = ON
long_query_time=10
slow_query_log_file = /<your_dir>/log/mysql_slow_query.log

# These are commonly set, remove the # and set as required.
# basedir = .....
datadir=/<your_dir>/data
port = 3306
# server_id = .....
socket = /<your_dir>/mysql.3306.sock
pid-file =/<your_dir>/mysql.3306.pid

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 
user=mysql
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

数据库启动

mysqld_safe --defaults-file=/<your_dir>/my.cnf

数据库登录

mysql --socket=mysql.3306.sock

数据库实例管理

查看数据库状态

mysqladmin --socket=mysql.3306.sock status

更改root密码：

mysqladmin -u root password root --socket=mysql.3306.sock

数据库关闭

mysqladmin -proot -uroot --socket=mysql.3306.sock shutdown

用户和权限管理

名为mysql的数据库中存放这元数据，其中use表与用户和权限有关。

use表的Host User Password列与用户登录有关，这三列可以确定登录用户的身份。

use表的Select_priv、Insert_priv等以priv结尾的列与用户权限有关，Y表示对所有表生效，N表示不对所有表生效。

使用数据库root用户登录数据库，并使用mysql数据库

mysql -uroot -proot --socket=mysql.3306.sock -D mysql

新建普通用户

create user 'username'@'host' identified by 'password'

其中host可以由%代替，表示对所有host登录的都适用。

或者

INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES('%','username',PASSWORD('password'),'','','');
FLUSH PRIVILEGES

或者

GRANT SELECT ON *.* TO 'username'@'%' identified by 'password';

其中*.*表示对所有数据库的所有表，这条语句可以在创建用户的同时给权限。

用户权限

查看权限

SHOW GRANT

赋予权限

GRANT SELECT,UPDATE,DELETE ON *.* TO 'username'@'%'

收回权限

REVOKE ALL ON *.* TO 'username'@'%'

FLUSH PRIVILEGES

删除用户

DROP USER 'username'@'%'

或者

DELETE FROM mysql.user WHERE Host = '%' AND User = 'username'

修改密码

使用命令mysqladmin -u -username -p password "new_password"

或者改表

UPDATE user SET Password = PASSWORD('new_password') WHERE USER = 'username' and Host = '%'

FLUSH PRIVILEGES

或者修改当前用户密码

SET PASSWORD = PASSWORD("new_password");

修改其他用户密码

SET PASSWORD FOR 'username'@'%'=PASSWORD("new_password")

配置mysql 远程登录的方法

默认情况下，mysql只允许本地登录，如果要开启远程连接，则需要修改/etc/mysql/my.conf文件。

一、修改/etc/mysql/my.conf
找到bind-address = 127.0.0.1这一行
改为bind-address = 0.0.0.0即可

二、为需要远程登录的用户赋予权限
1、新建用户远程连接mysql数据库
grant all on *.* to root@'%' identified by 'root' with grant option;
flush privileges;
允许任何ip地址(%表示允许任何ip地址)的电脑用root帐户和密码(root)来访问这个mysql server。
注意root账户不一定要存在。

2、支持root用户允许远程连接mysql数据库
grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;