3.6.9 Using AUTO_INCREMENT

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;

End - 3.6.9 Using AUTO_INCREMENT

Her er det samlet noen SQL-spørringer jeg tar vare på

Login: mysql -uuser -p -hhost.domain.com database (Reading history-file .mysql_history)
connect dbase; show databases; show tables; FLUSH PRIVILEGES;
Lage en ny tabell/base
CREATE TABLE guests ( guest_id int(4) unsigned zerofill DEFAULT '0000' NOT NULL auto_increment, guest_name varchar(50),
guest_email varchar(50), guest_time timestamp(14), guest_message text, PRIMARY KEY (guest_id)
); SHOW TABLES;
DESCRIBE TABLES;
se også i bunn her, eks. fra mysql.com
Vise diverse innhold:
SELECT * FROM `wiki1_pages` WHERE 1 AND `title` LIKE 'Hovedprosjekt' LIMIT 0, 30
SELECT * FROM guests WHERE guest_name='???';
SELECT guest_name,guest_email FROM guests;
Endre innhold
INSERT INTO guests ( guest_id, guest_name, guest_email, guest_time, guest_message ) values( 0000,'??','email', NULL,'Comment!');
UPDATE guests SET guest_email='newemail' WHERE guest_email='oldemail';
DELETE FROM guests;
DELETE FROM guests WHERE guest_name='??';
Hente og lage backup
mysqldump -uuser -p -hhost.domain.com database > dump-??-.sql
mysql -uuser -p -hhost.domain.com database < 2-dbase-??-.sql
http://63.240.93.141/content/images/020163466X/index/cheswickindex.pdf
resultat: "Resultatet så langt" -> ../t/poll.php?mode=results&poll
DROP TABLE `table1`, `table2`, `table3`;
UPDATE `table1` SET `counter` = '0' WHERE ``table1-id` = '1' LIMIT 140;
DELETE FROM `table1` WHERE table1_id = 1 AND table3_id = 6 LIMIT 1;
DELETE FROM `table2` WHERE table2_id = 1 AND ip = 'unknown';
UPDATE mysql.user SET Password=PASSWORD('notused') WHERE User='notdefined' AND Host='notdefined';
DELETE FROM mysql.user WHERE User='Isaidnotdefined';
FLUSH PRIVILEGES;
CREATE TABLE `table1` (
`table1_id` int(11) NOT NULL default '0',
`table1o_id` tinyint(4) NOT NULL default '0',
`table1o_text` varchar(255) NOT NULL default '',
`counter` int(11) NOT NULL default '0',
PRIMARY KEY (`table1p_id`,`table1o_id`),
FULLTEXT KEY `option_text` (`table1o_text`)
) TYPE=My_ISAM;
INSERT INTO `t_votes2` VALUES (1, 0, 'Iuke', 0);
INSERT INTO `t_votes2` VALUES (1, 1, 'Iuken', 0);
INSERT INTO `t_votes2` VALUES (1, 2, 'Iuka', 0);
INSERT INTO `t_votes2` VALUES (1, 3, 'Iugen', 0);
INSERT INTO `t_votes2` VALUES (1, 4, 'Iuga', 0);
INSERT INTO `t_votes2` VALUES (1, 5, 'Idagene', 0);
INSERT INTO `t_votes2` VALUES (1, 6, 'Lveka', 0
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
GRANT USAGE ON *.* TO 'dummy'@'localhost';
SHOW GRANTS FOR user

This statement lists the GRANT statements that must be issued to duplicate
the privileges for a user.

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

As of MySQL 4.1.2, to list privileges for the current session, you can use
any of the following statements:

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

Before MySQL 4.1.2, you can find out what user the session was
authenticated as by selecting the value of the CURRENT_USER() function (new
in MySQL 4.0.6). Then use that value in the SHOW GRANTS statement. See
section 13.8.3 Information Functions.

SHOW GRANTS is available as of MySQL 3.23.4.

-------------

User Comments
Posted by [name withheld] on August 21 2003 7:51am	[Delete] [Edit]

Selecting everything from mysql.user isn't quite the same as doing a SHOW
GRANTS for user@host. Ideally, MySQL should allow a subquery on "show",
where you could do "SHOW grants for (select concat(user,'@',host) from
mysql.user)". However, until then, this Perl script might help (substitute
"youruser" and "yourpassword" with details of a suitably privileged user):

#!/usr/bin/perl -w

use strict;
use DBI;
use Text::Wrap qw($columns &wrap);
z

my $dbase = "mysql";
my $dbuser = "youruser";
my $dbpassword = "yourpassword";
my $dbhost = "localhost";

my $dbh;
$dbh = DBI->connect( "DBI:mysql:$dbase:$dbhost", $dbuser, $dbpassword ) 
or die "can't open database ", $dbh->errstr, __LINE__;

my $statement = qq|SELECT User, Host from user |;

my $que = $dbh->prepare($statement);
my $result = $que->execute or die "error on database statement ",
$que->errstr, __LINE__;
my $tmp;
my $columns = 120;


while ( $tmp = $que->fetchrow_hashref ) {

  my $statement2 = qq| SHOW GRANTS for | . "'" . $tmp->{User} . "'\@'" .
  $tmp->{Host} . "'";
  
  my $que2 = $dbh->prepare($statement2);
  my $result2 = $que2->execute or die "error on database statement ",
  $que2->errstr, __LINE__;
  
  print qq(Privileges for $tmp->{User}\@$tmp->{Host}:\n\n);
  
  while ( my $tmp2 = $que2->fetchrow_hashref ) {
    print wrap( "", "", $tmp2->{ "Grants for $tmp->{User}\@$tmp->{Host}" } ), "\n\n";
  }
  print "-" x 120, "\n\n";
}
my $que2 = $dbh->disconnect;

Posted by Norbert Kremer on October 20 2003 11:15am	[Delete] [Edit]

The perl script provided by simon.ransome is very good, and runs as is.
However, the print formatting is not perfect. I think the author intended
the $columns variable to set the width of wrapping. If you remove the "my"
from this line: my $columns = 120; then the script will work as the author
intended. (in my case, I wanted to wrap at 200). Also, the line print "-" x
120, "\n\n"; could be changed to print "-" x $columns, "\n\n"; so that the
separator bar will be the same width as the wrapped text. Finally, it's not
a bad idea to put this at the end of the script (will occur implicitly, but
I like to clean up anyway) $dbh->disconnect;

------------------------------------------------------------------------------

The DROP USER statement deletes a MySQL account that doesn't have any
privileges. It serves to remove the account record from the user table. The
account is named using the same format as for GRANT or REVOKE; for example,
'jeffrey'@'localhost'. The user and host parts of the account name
correspond to the User and Host column values of the user table record for
the account.

To remove a MySQL user account, you should use the following procedure,
performing the steps in the order shown:

   1. Use SHOW GRANTS to determine what privileges the account has. See
section 14.5.3.10 SHOW GRANTS Syntax.
   2. Use REVOKE to revoke the privileges displayed by SHOW GRANTS. This
removes records for the account from all the grant tables except the user
table, and revokes any global privileges listed in the user table. See
section 14.5.1.2 GRANT and REVOKE Syntax.
   3. Delete the account by using DROP USER to remove the user table
record.

The DROP USER statement was added in MySQL 4.1.1. Before 4.1.1, you should
first revoke the account privileges as just described. Then delete the user
table record and flush the grant tables like this:

mysql> DELETE FROM mysql.user
    -> WHERE User='username' and Host='hostname';
mysql> FLUSH PRIVILEGES;

---------------------------


SETTE PASSORD
You can assign passwords to the root accounts several ways. The following
discussion demonstrates three methods:

    * By using the SET PASSWORD statement
    * By using the mysqladmin command-line client program
    * By using the UPDATE statement

To assign passwords using SET PASSWORD, connect to the server as root and
issue two SET PASSWORD statements, as follows:

shell> mysql -u root mysql
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');

Replace ``newpwd'' with the actual root password that you want to use. Be
sure to encrypt the password using the PASSWORD() function. In the second
command, replace host_name with the name of the server host. This is the
name that is specified in the Host column of the non-localhost record for
root in the user table. If you don't know what hostname this is, issue the
following statement before the SET PASSWORD statements:

mysql> SELECT Host, User FROM User;

Look for the record that has root in the User column and something other
than localhost in the Host column.

To assign passwords to the root accounts using mysqladmin, execute the
following commands:

shell> mysqladmin -u root password "newpwd"
shell> mysqladmin -u root -h host_name password "newpwd"

Replace ``newpwd'' with the actual root password that you want to use. The
double quotes are not always necessary, but you should use them if the
password contains spaces or other characters that are special to your
command interpreter. In the second command, replace host_name with the name
of the server host.

Another way to assign passwords is by using UPDATE to modify the user table
directly. Connect to the server as root and issue an UPDATE statement that
assigns a value to the Password column of the appropriate user table
records. The following UPDATE statement assigns a password to both root
accounts at once:

shell> mysql -u root mysql
mysql> UPDATE user SET Password = PASSWORD('newpwd')
    ->     WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

Replace ``newpwd'' with the actual root password that you want to use. Be
sure to encrypt the password using the PASSWORD() function.

Note that after you update the password in the user table directly using
UPDATE, you should tell the server to re-read the grant tables with FLUSH
PRIVILEGES. Otherwise, the change will go unnoticed otherwise until you
restart the server.

After the root password has been set, you must supply that password
whenever you connect to the server as root.

You might want to defer setting a password for the root accounts until
later, so that you don't need to specify it while you perform additional
setup or testing. However, be sure to set it before using your installation
for any real production work.

If you want to assign passwords to the anonymous accounts, you can use
either SET PASSWORD or UPDATE. In both cases, be sure to encrypt the
password using the PASSWORD() function.

To use SET PASSWORD, do this:

shell> mysql -u root mysql
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('newpwd');

The value of host_name is the same as when you assigned the root account
passwords.

To use UPDATE, do this:

shell> mysql -u root mysql
mysql> UPDATE user SET Password = PASSWORD('newpwd')
    ->     WHERE User = '';
mysql> FLUSH PRIVILEGES;

If you prefer to remove the anonymous accounts instead, do so as follows:

shell> mysql -u root mysql
mysql> DELETE FROM user WHERE User = '';
mysql> FLUSH PRIVILEGES;

Previous / Next / Up / Table of Contents

User Comments
Posted by [name withheld] on June 27 2002 8:50am	[Delete] [Edit]

That's so important. Check the files
into /var/lib/mysql and make sure the owner is
mysql. Do that with ls -al. If they are root then
change it by typing chown mysql *
That would might solve your problem if you are
trying to log in as root and you can't get it.
Posted by Chris on February 27 2002 6:49am	[Delete] [Edit]

Beware, when recreating the default security
settings by running mysql_install_db, check the
owner of the newly created files to make sure
that they are owned by the mysql user otherwise
you won't be able to restart the mysql service.
Posted by [name withheld] on December 18 2002 5:27pm	[Delete] [Edit]

Just to drop a note for beginners:
"mysqladmin -u root password
new_password"
this sets a new password for
root@LOCALHOST,
but not for the root@HOST.
By default the pass for root@HOST is blank so
just typing the command above is not sufficient.
For example your mysql server is completely open in
your local windows LAN....
so to correct the problem type:
"mysqladmin -u root -h my_host password
new_password"

Regards,
BIVOL
mail: bivolATinboxDOTru

Posted by Carey Black on November 19 2002 3:55pm	[Delete] [Edit]

I would suggest the following for "inital db lockdown
commands":

//login as root and use the mysql db
mysql -uroot mysql

//then issue these
UPDATE user SET Password=PASSWORD
('root_passwd') where user='root';
UPDATE user SET Password=PASSWORD
('guest_passwd') where user='';
FLUSH PRIVILEGES;

That should change the passwords for the users root
and for the "anonymous" users for all listed hosts.

I find it a bit of an odd construct that a user can
have a password that is host dependent. (If MySql
supported RSA key based authenticaiton then that
would make some sense, but I have yet to see that
in the documentation.) [I guess this "current
feature" has some value to someone.]
Posted by Frank Schacherer on April 17 2003 2:58am	[Delete] [Edit]

I first had the problem that the install script only inserts localhost and
the local host's machine name as allowed hosts for the root user. Beacuse
of this you could not connect as root from another machine. You can change
this by changing the allowed host for root to '%' like this:

UPDATE user SET host = '%'
WHERE user ='root' AND host = 'your.machine.com';
FLUSH PRIVILEGES;
Tilbake til HjemmeWiki