mysql建库实战

imitker MYSQL 5836 0

[root@iZ23xuh7nv7Z mysql]# cd bin/

[root@iZ23xuh7nv7Z bin]# ll

总计 344168

-rwxr-xr-x 1 mysql mysql 22303 2014-09-08 innochecksum

-rwxr-xr-x 1 mysql mysql 1458 2014-09-08 msql2mysql

……

-rwxr-xr-x 1 mysql mysql 111599 2014-09-08 mysqlaccess

-rwxr-xr-x 1 mysql mysql 1702 2014-09-08 mysqlaccess.conf

-rwxr-xr-x 1 mysql mysql 6835475 2014-09-08 mysqladmin

-rwxr-xr-x 1 mysql mysql 7446543 2014-09-08 mysqlbinlog

……

[root@iZ23xuh7nv7Z bin]# mysqld_safe --user=root &

[1] 24079

[root@iZ23xuh7nv7Z bin]# 160321 10:32:27 mysqld_safe Logging to '/alidata/log/mysql/error.log'.

160321 10:32:27 mysqld_safe The file /usr/local/mysql/bin/mysqld

does not exist or is not executable. Please cd to the mysql installation

directory and restart this script from there as follows:

./bin/mysqld_safe&

See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information

[1]+ Exit 1 mysqld_safe --user=root

[root@iZ23xuh7nv7Z bin]# ps aux | grep mysql

root 24310 0.0 0.0 63432 792 pts/0 S+ 10:32 0:00 grep mysql

mysql启动失败↑

[root@iZ23xuh7nv7Z bin]# vi /etc/my.cnf

# Example MySQL config file for medium systems.

……

# The following options will be passed to all MySQL clients

[client]

#password= your_password

port= 3306

socket= /tmp/mysql.sock

default-character-set=utf8①

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

port= 3306

socket= /tmp/mysql.sock

skip-external-locking

log-error=/alidata/log/mysql/error.log

key_buffer_size = 16M

max_allowed_packet = 1M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

character_set_server=utf8

lower_case_table_names=1

# Don't listen on a TCP/IP port at all. This can be a security enhancement,

……

#skip-networking

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin

# binary logging format - mixed recommended

binlog_format=mixed

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id= 1

# Replication Slave (comment out master section to use this)

……

#innodb_lock_wait_timeout = 50

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

[myisamchk]

key_buffer_size = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

expire_logs_days = 5

max_binlog_size = 1000M

[root@iZ23xuh7nv7Z bin]# /etc/init.d/mysqld restart

ERROR! MySQL server process #2179 is not running!

Starting MySQL... SUCCESS!

[root@iZ23xuh7nv7Z bin]# ps aux | grep mysql

root 24925 0.1 0.0 66252 1308 pts/0 S 10:46 0:00 /bin/sh /alidata/server/mysql/bin/mysqld_safe --datadir=/alidata/server/mysql/data --pid-file=/alidata/server/mysql/data/iZ23xuh7nv7Z.pid

mysql 25202 0.5 1.0 381052 40768 pts/0 Sl 10:46 0:00 /alidata/server/mysql/bin/mysqld --basedir=/alidata/server/mysql --datadir=/alidata/server/mysql/data --plugin-dir=/alidata/server/mysql/lib/plugin --user=mysql --log-error=/alidata/log/mysql/error.log --pid-file=/alidata/server/mysql/data/iZ23xuh7nv7Z.pid --socket=/tmp/mysql.sock --port=3306

root 25227 0.0 0.0 63432 792 pts/0 S+ 10:46 0:00 grep mysql

重启成功↑

[root@iZ23xuh7nv7Z bin]# mysql

mysql: Unknown OS character set 'GB18030'.

mysql: Switching to the default character set 'latin1'.

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

mysql连接失败↑①

[root@iZ23xuh7nv7Z bin]# mysql -u root

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

mysql连接失败↑

客户端mysql连接失败↑③

mysql安全模式启动↓

[root@iZ23xuh7nv7Z mysql]# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

[4] 25908

[root@iZ23xuh7nv7Z mysql]# 160321 10:58:14 mysqld_safe Logging to '/alidata/log/mysql/error.log'.

160321 10:58:14 mysqld_safe Starting mysqld daemon with databases from /alidata/server/mysql/data

[root@iZ23xuh7nv7Z mysql]# mysql -u root mysql

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 1

Server version: 5.5.40-log MySQL Community Server (GPL)

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> UPDATE user SET Password = PASSWORD('********') WHERE user = 'root';②设密码

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option;③赋权限

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> quit

Bye

[root@iZ23xuh7nv7Z mysql]# /etc/init.d/mysqld start

Starting MySQL SUCCESS!

数据库连接成功↓

[root@iZ23xuh7nv7Z mysql]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 3

Server version: 5.5.40-log MySQL Community Server (GPL)

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> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

4 rows in set (0.01 sec)

mysql> use mysql;

Database changed

mysql> show tables;

+---------------------------+

| Tables_in_mysql |

+---------------------------+

| columns_priv |

| db |

| event |

……

| user |

+---------------------------+

24 rows in set (0.00 sec)

mysql> select * from user;

+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+

| localhost | root | *9999C111A682**********309F22B82BE79AB0 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |

1 row in set (0.00 sec)

mysql> quit

[root@iZ23xuh7nv7Z bin]# mkdir /db_bak

[root@iZ23xuh7nv7Z bin]# mysqldump -h resource.remotedb.url -uuser -ppassword database> /db_bak/database.dump

[root@iZ23xuh7nv7Z bin]# ll /db_bak/

总计 34640

-rw-r--r-- 1 root root 35429255 03-21 11:32 database.dump

[root@iZ23xuh7nv7Z ~]# mysql -h 127.0.0.1 -uuser -ppassword database < /db_bak/database.dump

函数结构同步失败↓

[Msg] Starting Synchronization

[Msg] Source Server : prepDB1

[Msg] Source Database : webf

[Msg] Target Server : 127.0.0.0

[Msg] Target Database : webf

[Msg] Executing - CREATE FUNCTION `CreateMaxNo`(cNoType varchar(20),cNoLimit varchar(20)) RETURNS int(11) begin DECLARE tMaxNo integer DEFAULT 0; DECLARE isHasValue VARCHAR(20); select NoType into isHasValue from FDMaxNo where NoType = cNoType and NoLimit = cNoLimit; IF ISNULL(isHasValue) THEN Insert Into FDMaxNo (NOTYPE,NOLIMIT,MAXNO) values(cNoType,cNoLimit,1); SET tMaxNo= 1; ELSE update FDMaxNo set MaxNo = MaxNo+1 where NoType = cNoType and NoLimit = cNoLimit; SELECT MaxNo INTO tMaxNo from FDMaxNo where NoType = cNoType and NoLimit = cNoLimit; End If; return tMaxNo; end

[Msg] Failed - Error on Function CreateMaxNo - 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

[Err] Synchronization Failed

解决方法：

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

表不存在的错误↓

2016-03-21 15:46:18,790 ERROR [STDERR] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'webf2.JDUser' doesn't exist

2016-03-21 15:46:18,791 ERROR [STDERR] at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

2016-03-21 15:46:18,791 ERROR [STDERR] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

2016-03-21 15:46:18,791 ERROR [STDERR] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

2016-03-21 15:46:18,794 ERROR [STDERR] java.lang.NullPointerException

2016-03-21 15:46:18,794 ERROR [STDERR] at com.sinosoft.jbs.db.JDUserDB.getInfo(JDUserDB.java:815)

2016-03-21 15:46:18,795 ERROR [STDERR] at com.sinosoft.jbs.menumang.JDUserBL.queryUser(JDUserBL.java:120)

2016-03-21 15:46:18,795 ERROR [STDERR] at com.sinosoft.jbs.menumang.JDUserBL.submitData(JDUserBL.java:77)