mysql初始化设置_MySQL初始化设置

1 初始化数据:

/usr/local/mysql/bin/mysqld –initialize-insecure –user=mysql –datadir=/opt/mysql/data –basedir=/opt/mysql

2 配置文件

vim /etc/my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/mydata

socket=/tmp/mysql.sock

log_error=/var/log/mysql.log

user=mysql

port=6606

[mysql]

socket=/tmp/mysql.sock

作用:

1.影响服务端的启动

标签: [mysqld] [mysqld_safe] [server] …

[mysqld]

basedir=/opt/mysql

datadir=/opt/mysql/data

user=mysql

socket=/tmp/mysql.sock

port=3306

server_id=6

2.影响客户端连接

标签: [client] [mysql] [mysqldump] ….

[mysql]

socket=/tmp/mysql.sock

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

3 多实例(3307 3308 3309)

3.1 创建相关目录

mkdir -p /data/330{7..9}/data

3.2 创建配置文件

cat>> /data/3307/my.cnf<

[mysqld]

basedir=/opt/mysql

datadir=/data/3307/data

user=mysql

socket=/data/3307/mysql.sock

port=3307

server_id=3307

EOF

cp /data/3307/my.cnf /data/3308

cp /data/3307/my.cnf /data/3309

sed -i ‘s#3307#3308#g‘ /data/3308/my.cnf

sed -i ‘s#3307#3309#g‘ /data/3309/my.cnf

3.3 初始化数据

mysqld –initialize-insecure –user=mysql –datadir=/data/3307/data –basedir=/opt/mysql

mysqld –initialize-insecure –user=mysql –datadir=/data/3308/data –basedir=/opt/mysql

mysqld –initialize-insecure –user=mysql –datadir=/data/3309/data –basedir=/opt/mysql

3.4 启动多实例

chown -R mysql.mysql /data/*

mysqld_safe –defaults-file=/data/3307/my.cnf &

mysqld_safe –defaults-file=/data/3308/my.cnf &

mysqld_safe –defaults-file=/data/3309/my.cnf &

4 测试

netstat -lnp|grep 330

mysql -S /data/3307/mysql.sock

mysql -S /data/3308/mysql.sock

mysql -S /data/3309/mysql.sock

5 systemd管理多实例

cat >> /etc/systemd/system/mysqld3307.service <

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

[Service]

User=mysql

Group=mysql

ExecStart=/opt/mysql/bin/mysqld –defaults-file=/data/3307/my.cnf

LimitNOFILE = 5000

EOF

cp /etc/systemd/system/mysqld3307.service /etc/systemd/system/mysqld3308.service

cp /etc/systemd/system/mysqld3307.service /etc/systemd/system/mysqld3309.service

sed -i ‘s#3307#3308#g‘ /etc/systemd/system/mysqld3308.service

sed -i ‘s#3307#3309#g‘ /etc/systemd/system/mysqld3309.service

systemctl start mysqld3307

systemctl start mysqld3308

systemctl start mysqld3309

netstat -lnp|grep 330

systemctl stop mysqld3309

systemctl stop mysqld3308

systemctl stop mysqld3307

systemctl enable mysqld3307

systemctl enable mysqld3308

systemctl enable mysqld3309

6.忘记密码处理

mysqladmin -uroot -p password 123

select user,authentication_string,host from mysql.user;

1.停数据库

/etc/init.d/mysqld stop

2.启动数据库为无密码验证模式

mysqld_safe –skip-grant-tables –skip-networking &

update mysql.user set authentication_string=PASSWORD(‘456‘) where user=‘root‘ and host=‘localhost‘;

/etc/init.d/mysqld restart

[[email protected] ~]# mysql -uroot -p123

[[email protected] ~]# mysql -uroot -p456

7.数据类型和字符集

整型

int 最多存10位数字

-2^31 ~ 2^31-1

2^32 10位数 11

浮点

字符串类型

char 定长,存储数据效率较高,对于变化较多的字段,空间浪费较多

varchar 变长,存储时判断长度,存储会有额外开销,按需分配存储空间.

enum

时间

datetime

timestamp

date

time

SQL语句规范第五条: 1.少于10位的数字int ,大于10位数 char,例如手机号 2.char和varchar选择时,字符长度一定不变的可以使用char,可变的尽量使用varchar 在可变长度的存储时,将来使用不同的数据类型,对于索引树的高度是有影响的. 3.选择合适的数据类型 4.合适长度