mysql建库实战

[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)

Copyright (c) 2000, 2014, 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> 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)

Copyright (c) 2000, 2014, 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> 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;

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

| Host      | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | SSL_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |

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

| 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)

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

2016-03-21 15:46:18,795 ERROR [STDERR] at org.apache.jsp.logon.LoginSubmit_jsp._jspService(LoginSubmit_jsp.java:129)

2016-03-21 15:46:18,795 ERROR [STDERR] at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)

2016-03-21 15:46:18,795 ERROR [STDERR] at javax.servlet.http.httpservlet.service(HTTPServlet.java:717)

解决方法：

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

[mysqld]

lower_case_table_names=1

:wq!

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