ubuntu mysql 5.0_Ubuntu 1204 Mysql Server 5.0 数据库和表的操作

最新推荐文章于 2024-06-25 04:00:00 发布

吴绍贵

最新推荐文章于 2024-06-25 04:00:00 发布

阅读量269

点赞数

文章标签： ubuntu mysql 5.0

本文链接：https://blog.csdn.net/weixin_33168819/article/details/113279906

版权

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

5 rows in set (0.00 sec)

mysql>

本文详细介绍Ubuntu下MySQL数据库安装后初步设置。

1、安装MySQL 这个应该很简单了，而且我觉得大家在安装方面也没什么太大问题，所以也就不多说了，下面我们来讲讲配置。

2、配置MySQL

注意，在Ubuntu下MySQL缺省是只允许本地访问的，如果你要其他机器也能够访问的话，那么需要改变/etc/mysql/my.cnf配置文件了！

默认的MySQL安装之后根用户是没有密码的，所以首先用根用户进入： $mysql -u root 在这里之所以用-u

root是因为我现在是一般用户(firehare)，如果不加-u

root的话，mysql会以为是firehare在登录。

注意，我在这里没有进入根用户模式，因为没必要。一般来说，对mysql中的数据库进行操作，根本没必要进入根用户模式，只有在设置时才有这种可能。进入mysql之后，最要紧的就是要设置Mysql中的root用户密码了，否则，Mysql服务无安全可言了。

mysql> GRANT ALL PRIVILEGES ON *.* TO

root@localhost IDENTIFIED BY "123456";

注意，我这儿用的是123456做为root用户的密码，但是该密码是不安全的，请大家最好使用大小写字母与数字混合的密码，且不少于8位。这样的话，就设置好了MySQL中的root用户密码了，然后就用root用户建立你所需要的数据库。

我这里就以xoops为例：

mysql>CREATE DATABASE xoops；

mysql>

GRANT ALL PRIVILEGES ON xoops.* TO xoops_root@localhost

IDENTIFIED BY "654321";

这样就建立了一个xoops_roots的用户，它对数据库xoops有着全部权限。以后就用xoops_root来对xoops数据库进行管理，而无需要再用root用户了，而该用户的权限也只被限定在xoops数据库中。

如果你想进行远程访问或控制，那么你要做两件事：

其一：

mysql>GRANT ALL PRIVILEGES ON xoops.* TO

xoops_root@"%" IDENTIFIED BY "654321";

允许xoops_root用户可以从任意机器上登入MySQL。

其二：

$sudo gedit /etc/mysql/my.cnf >skip-networking

=> # skip-networking

这样就可以允许其他机器访问MySQL了。

在Ubuntu下连接数据库的GUI软件：mysql-admin mysql-query-browser。

dapper升级到MySQL5+PHP5出现乱码的处理办法问题发生在采用gbk为网站内码的数据。

一：导出数据：

mysqldump -uroot -p --default-character-set

=latin1 --set-charset=gbk --skip-opt mydatabse >

d.sql

二：建立新的数据库：

CREATE DATABASE `newmydatabase` DEFAULT

CHARACTER SET gbk COLLATE gbk_chinese_ci;

三：重新导入数据：

mysql -uroot -p --default-character-set=

gbk -f newmydatabase >source d.sql .....

>exit

1.在终端启动：mysql -uroot

-p密码然后回车；

2.查看数据库的名字：SHOW

DATABASES;；

3.选择一个数据库：USE

test_STinfo;；(今天总是SELECT，惭愧。)

4.查看已选择数据库中表的名字：SHOW

TABLES;；

5.查看表的详细情况：DESC

T_teacher;；

6.创建一个数据库：CREATE

DATABASE test_51cto;；

7.(USE test_51cto)创建表：CREATE

TABLE test_table(51ctoID INT(8)PRIMARY KET NOT NULL,

51ctoName VARCHAR(20)NOT

NULL);；

8.修改表(增加列)：ALTER TABLE

test_table ADD 51ctoScore INT(8) NOT NULL;；

9.修改表(修改列)：ALTER TABLE

test_table MODIFY 51ctoScore DOUBLE(8,1) NOT NULL;；

10.修改表(删除列)：ALTER TABLE

test_table DROP 51ctoScore;；

11.删除一个数据库：DROP DATABASE

test_51cto;；

12.退出mysql：exit；(全文的语句只有这不用分号，

)。

To find the Ubuntu Server Guide related to your

specific version, please go to: https://help.ubuntu.com/, select

your Ubuntu Server version and then click on Ubuntu Server

Guide. For the latest version (11.04) of Ubuntu Server,

please go to https://help.ubuntu.com/11.04/serverguide/C/httpd.html

This is to help people setup and install a

LAMP

(Linux-Apache-MySQL-PHP)

server in Ubuntu, including Apache 2, PHP 5 and MySQL 4.1 or 5.0.

install the default LAMP stack in Ubuntu 10.04 and above

First install tasksel...

sudo apt-get install tasksel

... and then the LAMP stack:

sudo tasksel install lamp-server

See Tasksel - be

warned, only use tasksel to install tasks, not to remove them - see

https://launchpad.net/bugs/574287

Starting over:

How to remove the LAMP stack

To remove the LAMP stack remove the following

packages:

Note: This assumes you have no other programs that require any

of these packages. You might wish to simulate this removal first,

and only remove the packages that don't cause removal of something

desired.

apache2 apache2-mpm-prefork apache2-utils apache2.2-common libapache2-mod-php5 libapr1 libaprutil1 libdbd-mysql-perl libdbi-perl libmysqlclient15off libnet-daemon-perl libplrpc-perl libpq5 mysql-client-5.0 mysql-common mysql-server mysql-server-5.0 php5-common php5-mysql

To also remove the debconf data, use the purge

option when removing. To get rid of any configurations you may have

made to apache, manually remove the /etc/apache2 directory once the

packages have been removed.

Installing Apache 2

To only install the apache2 webserver, use

any

method to install

apache2

It requires a restart for it to work

sudo /etc/init.d/apache2 restart

Checking Apache 2

installation

With your web browser, go to the URI

http://localhost : if you read "It works!", which

is the content of the file /var/www/index.html , this proves Apache

works.

Troubleshooting Apache

If you get this error:

apache2: Could not determine the server's

fully qualified domain name, using 127.0.0.1 for ServerName

then use a text editor such as "sudo nano" at

the command line or "gksudo gedit" on the desktop to create a new

file,

sudo nano /etc/apache2/conf.d/fqdn

gksu "gedit /etc/apache2/conf.d/fqdn"

then add

ServerName localhost

to the file and save. This can all be done in a

single command with the following:

echo "ServerName localhost" | sudo tee /etc/apache2/conf.d/fqdn

Virtual Hosts

Apache2 has the concept of sites, which are

separate configuration files that Apache2 will read. These are

available in /etc/apache2/sites-available. By default,

there is one site available called default this is what

you will see when you browse to http://localhost or http://127.0.0.1. You can have many

different site configurations available, and activate only those

that you need.

As an example, we want the default site to be

/home/user/public_html/. To do this, we must create a new

site and then enable it in Apache2.

To create a new site:

Copy the default website as a starting point.

sudo cp /etc/apache2/sites-available/default /etc/apache2/sites-available/mysite

Edit the new configuration file in a text editor

"sudo nano" on the command line or "gksudo gedit", for example:

gksudo gedit /etc/apache2/sites-available/mysite

Change the DocumentRoot to point to the

new location. For example, /home/user/public_html/

Change the Directory directive, replace

You can also set separate logs for each

site. To do this, change the ErrorLog and

CustomLog directives. This is optional, but handy if you

have many sites

Save the file

Now, we must deactivate the old site, and

activate our new one. Ubuntu provides two small utilities that take

care of this: a2ensite

(apache2enable

site) and a2dissite

(apache2disable

site).

sudo a2dissite default && sudo a2ensite mysite

Finally, we restart Apache2:

sudo /etc/init.d/apache2 restart

If you have not created

/home/user/public_html/, you will receive an warning

message

To test the new site, create a file in

/home/user/public_html/:

echo 'Hello! It is working!' > /home/user/public_html/index.html

Finally, browse to http://localhost/

Installing PHP 5

To only install PHP5. use any

method to install the package

libapache2-mod-php5

Enable this module by doing

sudo a2enmod php5

which creates a symbolic link

/etc/apache2/mods-enabled/php5 pointing to

/etc/apache2/mods-availble/php5 .

Except if you use deprecated PHP code beginning

only by "" instead of "<?php "

(which is highly inadvisable), open, as root, the file

/etc/php5/apache2/php.ini , look for the line

"short_open_tag = On", change it to

"short_open_tag = Off" (not including the quotation marks) and add

a line of comment (beginning by a semi-colon) giving the reason,

the author and the date of this change. This way, if you later want

some XML or XHTML file to be served as PHP, the

"<?xml " tag will be ignored by PHP instead of being

seen as a PHP code mistake.

Relaunch Apache 2 by

sudo service apache2 restart

Checking PHP 5

installation

In /var/www , create a text file called

"test.php", grant the world (or, at least, Ubuntu user "apache")

permission to read it, write in it the only line:

"<?php phpinfo(); ?>" (without the

quotation marks) then, with your web browser, go to the URI

"http://localhost/test.php": if you

can see a description of PHP5 configuration, it proves PHP 5 works

with Apache.

Troubleshooting PHP 5

Does your browser ask if you want to

download the php file instead of displaying it? If

Apache is not actually parsing the php after you restarted it,

install libapache2-mod-php5. It is installed when you

install the php5 package, but may have been removed

inadvertently by packages which need to run a different version of

php.

sudo a2enmod php5

returns "$ This module does not exist!", you should purge (not just

remove) the libapache2-mod-php5 package and reinstall it.

Be sure to clear your browser's cache before

testing your site again. To do this in Firefox 4: Edit →

Preferences … Privacy → History: clear your recent history →

Details : choose "Everything" in "Time range to clean" and check

only "cache", then click on "Clear now".

Remember that, for Apache to be called, the URI

in your web browser must begin with "http://". If it begins with "file://", then the file is read directly by the browser,

without Apache, so you get (X)HTML and CSS, but no PHP. If you

didn't configure any host alias or virtual host, then a local URI

begins with "http://localhost", "http://127.0.0.1" or http://" followed by your IP number.

If the problem persists, check your PHP file

authorisations (it should be readable at least by Ubuntu user

"apache"), and check if the PHP code is correct. For instance, copy

your PHP file, replace your whole PHP file content by

"<?php phpinfo(); ?>" (without the

quotation marks): if you get the PHP test page in your web browser,

then the problem is in your PHP code, not in Apache or PHP

configuration nor in file permissions. If this doesn't work, then

it is a problem of file authorisation, Apache or PHP configuration,

cache not emptied, or Apache not running or not restarted. Use the

display of that test file in your web browser to see the list of

files influencing PHP behaviour.

Installing MYSQL with PHP

Use any

method to install

mysql-server libapache2-mod-auth-mysql php5-mysql

After installing PHP

You may need to increase the memory limit that

PHP imposes on a script. Edit the /etc/php5/apache2/php.ini file

and increase the memory_limit value.

After installing MySQL

Set mysql bind address

Before you can access the database from

other computers in your network, you have to change its

bind address. Note that this can be a security problem,

because your database can be accessed by other computers than your

own. Skip this step if

the applications which require mysql are running on the same

machine.

type:

nano /etc/mysql/my.cnf

and change the line:

bind-address = localhost

to your own internal ip address e.g.

192.168.1.20

bind-address = 192.168.1.20

If your ip address is dynamic you can also

comment out the bind-address line and it will default to your

current ip.

If you try to connect without changing the

bind-address you will recieve a "Can not connect to mysql error

10061".

Set mysql root password

Before accessing the database by console you

need to type:

mysql -u root

At the mysql console type:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yourpassword');

A successful mysql command will show:

Query OK, 0 rows affected (0.00 sec)

Mysql commands can span several lines. Do not

forget to end your mysql command with a semicolon.

Note: If you have already set a

password for the mysql root, you will need to use:

mysql -u root -p

(Did you forget the mysql-root password? See

MysqlPasswordReset.)

Create a mysql database

mysql> CREATE DATABASE database1;

Create a mysql user

For creating a new user with all privileges (use

only for troubleshooting), at mysql prompt type:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'yourusername'@'localhost' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;

For creating a new user with fewer privileges

(should work for most web applications) which can only use the

database named "database1", at mysql prompt type:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON database1.* TO 'yourusername'@'localhost' IDENTIFIED BY 'yourpassword';

yourusername and yourpassword

can be anything you like. database1 is the name of the

database the user gets access to. localhost is the

location which gets access to your database. You can change it to

'%' (or to hostnames or ip addresses) to allow connections from

every location (or only from specific locations) to the database.

Note, that this can be a security problem and should only

be used for testing purposes!

To exit the mysql prompt type:

mysql> \q

Since the mysql root password is now set, if you

need to use mysql again (as the mysql root), you will need to use:

mysql -u root -p

and then enter the password at the prompt.

Backup-Settings

Please, let's say something in which directories

mysql stores the database information and how to configure a backup

Alternatively

There is more than just one way to set the mysql

root password and create a database. For example

mysqladmin can be used:

mysqladmin -u root -p password yourpassword

and

mysqladmin -u root -p create database1

mysqladmin is a command-line tool

provided by the default LAMP install.

Phpmyadmin and mysql-admin

All mysql tasks including setting the root

password and creating databases can be done via a graphical

interface using phpmyadmin or

mysql-admin.

To install one or both of them, first enable the

universe repository

Use any

method to install

phpmyadmin

Troubleshooting

Phpmyadmin & mysql-admin

If you get blowfish_secret

error: Choose

and set a phrase for cryptography in the file

/etc/phpmyadmin/blowfish_secret.inc.php and copy the line (not the

php tags) into the file /etc/phpmyadmin/config.inc.php or you will

receive an error.

If you get a 404 error upon visiting

http://localhost/phpmyadmin:

You will need to

configure apache2.conf to work with Phpmyadmin.

sudo gedit /etc/apache2/apache2.conf

Include the following line at the bottom of the

file, save and quit.

Include /etc/phpmyadmin/apache.conf

Alternative:

install phpMyAdmin from source

See the phpMyAdmin page

for instructions on how to install phpmyadmin from source:

Mysql-admin

Mysql-admin runs locally, on the desktop. Use

any

method to install

mysql-admin

For more information

Edit Apache Configuration

You may want your current user to be the PHP

pages administrator. To do so, edit the Apache configuration file :

$ gksudo "gedit /etc/apache2/apache2.conf"

Search both the strings starting by "User" and

"Group", and change the names by the current username and groupname

you are using. Then

you'll need to restart Apache. (look at the next chapter concerning

apache commands)

Configuration options relating specifically to

user websites (accessed through localhost/~username) are in

/etc/apache2/mods-enabled/userdir.conf.

Installing suPHP

suPHP is a tool for executing PHP scripts with

the permissions of their owners. It consists of an Apache module

(mod_suphp) and a setuid root binary (suphp) that is called by the

Apache module to change the uid of the process executing the PHP

interpreter.

Note: suPHP enforces, security and helps avoid

file permission problems under development environments with

several users editing the site files, but it also demands more

memory and CPU usage, which can degrade your server performance

under certain circumstances.

To only install suPHP. use any

method to install the package

libapache2-mod-suphp

Enable this module by doing

sudo a2enmod suphp

then use a text editor such as "sudo nano" at

the command line or "gksudo gedit" on the desktop to edit this file

sudo nano /etc/apache2/mods-available/php5.conf

gksu "gedit /etc/apache2/mods-available/php5.conf"

make a new empty line at the top of the content,

then add

make a new empty line at the bottom of the

content, then add

save changes

For security reasons we need to specify to suPHP

what are the document paths allowed to execute scripts, use a text

editor such as "sudo nano" at the command line or "gksudo gedit" on

the desktop to edit this file

sudo nano /etc/suphp/suphp.conf

gksu "gedit /etc/suphp/suphp.conf

find the value "docroot" and specify the

document path of your site files, for example:

docroot=/var/www/

that value restrict script execution only to

files inside "/var/www/"

docroot=/var/www/:${HOME}/public_html

that value restrict script execution only to

files inside a custom home folder for each configured user inside

"/var/www/:${HOME}/public_html"

for this tutorial we are going to use this value

docroot=/home/user/public_html/

which is the same Apache directory directive set

before in this document

save changes

to restart Apache, type in your terminal

sudo /etc/init.d/apache2 restart

Now lets create a test script to see if suPHP is

working correctly, in your terminal type

echo "<?php echo 'whoim = '.exec('/usr/bin/whoami');?>" | tee /home/user/public_html/whomi.php

that command creates a quick php test file to

display the current user executing the script

open your browser and navigate to

"localhost/whomi.php", most likely the browser will show you a

"500" server error, this is because suPHP does not allow too

permissive file and folder permissions and also does not allow

mixed file and folder ownership, to correct this type in your

terminal

sudo find /home/user/public_html/ -type f -exec chmod 644 {} \;

sudo find /home/user/public_html/ -type d -exec chmod 755 {} \;

sudo chown user:group -R /home/user/public_html/

those commands enforce a secure and correct file

and folder permission and also set a correct user and group

ownership for all of them

Now open your browser and navigate to

"localhost/whomi.php", if everything went fine you should see the

name of the file owner executing the script and not "www-data"

unless you specified so

Run, Stop, Test,

And Restart Apache

Use the following command to run Apache :

$ sudo /usr/sbin/apache2ctl start

To stop it, use :

$ sudo /usr/sbin/apache2ctl stop

To test configuration changes, use :

$ sudo /usr/sbin/apache2ctl configtest

Finally, to restart it, run :

$ sudo /usr/sbin/apache2ctl restart

Alternatively, you can use a graphical interface

by installing Rapache or the simpler

localhost-indicator.

Using Apache

You can access apache by typing 127.0.0.1 or

http://localhost (by

default it will be listening on port 80) in your browser address

bar. By default the

directory for apache server pages is /var/www . It needs root access in order to put

files in. A way to do it is just starting the file browser as root

in a terminal:

$ sudo nautilus

if you want to make /var/www your own. (Use only

for non-production web servers - this is not the most secure way to

do things.)

$ sudo chown -R $USER:$USER /var/www

Status

To check the status of your PHP installation:

$ gksudo "gedit /var/www/testphp.php"

and insert the following line

Securing Apache

If you just want to run your Apache install as a

development server and want to prevent it from listening for

incoming connection attempts, this is easy to do.

$ gksudo "gedit /etc/apache2/ports.conf"

$ password:

Change ports.conf so that it contains:

Listen 127.0.0.1:80

Save this file, and restart Apache (see above).

Now Apache will serve only to your home domain, http://127.0.0.1 or http://localhost.

Password-Protect a