mysql 初学
mysql 初学
胡子叭槎 发表于1年前
mysql 初学
  • 发表于 1年前
  • 阅读 41
  • 收藏 1
  • 点赞 0
  • 评论 0

【腾讯云】如何购买服务器最划算?>>>   

1    本地安装mysql

1.1 本地安装MySQL

    yum install mysql.x86_64 mysql-server.x86_64

1.2    启动mysql

    [root@wzlvm ~]# /etc/init.d/mysqld start

1.3    创建数据库

   1. 3.1 进入MySQL

[root@wzlvm ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

    1.3.2  创建数据库

        

mysql> show databases; # 查看默认有哪些数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> create database base1;    # 创建数据库
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| base1              |    #=======》 刚刚创建的数据库
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

# 创建用户
insert into mysql.user(Host,User,Password)values('localhost','user1','123456');

# 授权 数据库base1 里面所有的表的所有权限都授权给用户user1从任意位置登录访问操作
grant all on base1.* to user1@'%';

# 上面的方法有点情况不能正常登陆,建议采用以下代码一次性创建用户和授权
grant all on base1.* to user3@'%' identified by '123456';

 

   1. 3.3 建表

        先进入建好的数据库

mysql> use base1;
Database changed

        建表

mysql> create table table1 (id int,remark varchar(200));
Query OK, 0 rows affected (0.03 sec)

    插入数据

mysql> insert into table1(id,remark)values(1,'test1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from table1;
+------+--------+
| id   | remark |
+------+--------+
|    1 | test1  |
+------+--------+
1 row in set (0.00 sec)

注意:有时候数据库表用户都建好列,但是就是链接不上,例如:

>>> db = pymysql.connect(host='127.0.0.1',user='wangzilong',password='123456',db='base1')
Traceback (most recent call last):
  File "<input>", line 1, in <module>
    db = pymysql.connect(host='127.0.0.1',user='wangzilong',password='123456',db='base1')
  File "/data/program/python2.7/lib/python2.7/site-packages/pymysql/__init__.py", line 90, in Connect
    return Connection(*args, **kwargs)
  File "/data/program/python2.7/lib/python2.7/site-packages/pymysql/connections.py", line 688, in __init__
    self.connect()
  File "/data/program/python2.7/lib/python2.7/site-packages/pymysql/connections.py", line 906, in connect
    self._request_authentication()
  File "/data/program/python2.7/lib/python2.7/site-packages/pymysql/connections.py", line 1114, in _request_authentication
    auth_packet = self._read_packet()
  File "/data/program/python2.7/lib/python2.7/site-packages/pymysql/connections.py", line 981, in _read_packet
    packet.check_error()
  File "/data/program/python2.7/lib/python2.7/site-packages/pymysql/connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "/data/program/python2.7/lib/python2.7/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
OperationalError: (1045, u"Access denied for user 'wangzilong'@'localhost' (using password: YES)")

可能是mysql.user 表中存在用户名为空定用户,删除空用户重启mysql因该就好了

注意:由于本地安装mysql一般版本都比较低很多功能都无法使用,所以不可取。下面介绍企业经常使用的二进制安装

2    二进制安装mysql

    2.1 添加20G的磁盘

    添加磁盘通过lvm添加,有利于以后磁盘空间扩展

    添加详细步骤就不多介绍,之前已经有写过。

    2.2 给mysql添加用户和组都是mysql

    groupadd mysql

    useradd -r -g mysql mysql

# mysql用户
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash

# mysql 组
mysql:x:27:

    2.3 挂载点路径下创建指定的目录,并把二进制安装包解压后的文件全部copy到product文件夹里面

[root@wzlvm mysql]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3              18G  3.0G   14G  18% /
tmpfs                 495M     0  495M   0% /dev/shm
/dev/sda1             1.9G   62M  1.8G   4% /boot
/dev/sr0              3.7G  3.7G     0 100% /data/local_cdrom
/dev/mapper/vgmysql-mysql
                      9.8G  1.2G  8.1G  13% /data/program/mysql  # 刚刚新增的磁盘并创建了lvm 挂载在这个路径下

载挂载点下面创建指定的文件夹:

mkdir -p /data/program/mysql/product
mkdir -p /data/program/mysql/data
mkdir -p /data/program/mysql/log
mkdir -p /data/program/mysql/backup
mkdir -p /data/program/mysql/tmp
mkdir -p /data/program/mysql/scripts

[root@wzlvm mysql]# ll
total 24
drwxr-xr-x  2 mysql mysql 4096 Nov  7 22:24 backup
drwxr-xr-x  5 mysql mysql 4096 Nov  7 23:03 data
drwxr-xr-x  2 mysql mysql 4096 Nov  7 22:55 log
drwxr-xr-x 13 mysql mysql 4096 Nov  7 22:47 product
drwxr-xr-x  2 mysql mysql 4096 Nov  7 22:25 scripts
drwxr-xr-x  2 mysql mysql 4096 Nov  7 22:55 tmp

解压二进制安装包:

tar -zxvf mysql-5.6.20-linux-glibc2.5-x86_64.tar.gz

进入解压之后的目录下,全部移动到上面的product文件夹里面:

mv * /data/program/mysql/product/

给挂载点设置用户和组:

chown -R mysql:mysql /data/program/mysql

切换到mysql用户下:

su - mysql

进入目录:cd /data/program/mysql/product/scripts

设置mysql安装参数:

./mysql_install_db --user=mysql --datadir=/data/program/mysql/data --defaults-file=/data/program/mysql/product/my.cnf --basedir=/data/program/mysql/product/

注意:上面的路径要和我们手动创建的product路径一致

切换到root用户下:cp /data/program/mysql/product/support-files/mysql.server /etc/init.d/mysqld

配置mysql的启动文件:

vi /etc/init.d/mysqld
basedir=/data/program/mysql/product
datadir=/data/program/mysql/data

vim /data/program/mysql/product/my.cnf
[client]
port            = 3369
socket = /data/program/mysql/tmp/mysql.sock
default-character-set=utf8
[mysqld]
port = 3369
server_id = 5
basedir = /data/program/mysql/product
datadir = /data/program/mysql/data
socket = /data/program/mysql/tmp/mysql.sock
pid_file=/data/program/mysql/tmp/mysql.pid
log_error = /data/program/mysql/log/error.log
innodb_buffer_pool_size = 8192M
innodb_file_per_table=1
tmp_table_size=64M
max_heap_table_size=64M
max_connections=600
character_set_server=utf8
autocommit=on
transaction_isolation=read-committed
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip-name-resolve=on
explicit_defaults_for_timestamp=on
lower_case_table_names=1
read_only=off
log_bin = /data/program/mysql/log/binlog
max_binlog_size=256m
binlog_format=row
expire_logs_days=30
innodb_log_file_size = 256m
innodb_log_files_in_group = 3
log_slave_updates=on
innodb_flush_log_at_trx_commit=0
log_bin_trust_function_creators=1
slow_query_log_file = /data/program/mysql/log/slow.log
slow_query_log=on
long_query_time=1
log_queries_not_using_indexes=on
/data/program/mysql/product/bin/mysqld_safe --defaults-file=/data/program/mysql/product/my.cnf &
mysql -uroot -p --socket=/data/program/mysql/tmp/mysql.sock
set password=password('123456');
flush privileges;

vi /etc/profile 加入以下信息
##database
export MYSQL_HOME=/data/program/mysql/product
export PATH=$PATH:/data/program/mysql/product/bin

注意:/etc/my.cnf 文件也要进行配置:


  1 [mysqld]
  2 datadir=/data/program/mysql/data
  3 socket=/data/program/mysql/tmp/mysql.sock
  4 user=mysql
  5 # Disabling symbolic-links is recommended to prevent assorted security risks
  6 symbolic-links=0
  7 
  8 [mysqld_safe]
  9 log-error=/data/program/mysql/log/error.log
 10 pid-file=/data/program/mysql/tmp/mysql.pid

启动mysql:

/etc/init.d/mysqld start

登录mysql:

mysqladmin -uroot password '123456' 修改密码

mysql -uroot -p 登录

查看数据库:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

创建数据库,创建表,都在上面有说明,这里就不多说明了。

 

 

标签: mysql
共有 人打赏支持
粉丝 20
博文 122
码字总数 124527
×
胡子叭槎
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: