create database yourdbname;
Show All Databases:
Drop / Delete a MySQL Database:
drop database nameofyourdatabase;........
This is most likely to happen on a normal GUI system like Ubuntu or Linux Mint. If you or the user is meant to have sudo / root privileges it is as simple as editing the following files:
Now assume your username is "iamtheuser"
Find the above lines and add a comma and "ia........
GRANT SELECT, LOCK TABLES ON *.* TO yourmysqluser@localhost;
All you need to do a full MySQL dump on all databases is the SELECT and LOCK TABLES privileges. This way you don't have to use the mysql root user. Data could be compromised this way but at least no harm from manipulation, changes or deletion are possible by locking down the privileges to the minimum for a full MySQL dump and backup.........
The commands below will help you reset any mysql user password.
update user set password=PASSWORD('thenewpass') where User='theusername';
The first line says to use the "mysql" database which contains all the user info.
the second update line sets the new password "thenewpass" for the u........
This is a common issue, what if a issue shouldn't have root but you want to use that user to make a full backup of a system? They of course need root access.
You can actually just give them passwordless sudo access to rsync in /etc/sudoers:
sudo vi /etc/sudoers
yourusername ALL = NOPASSWD: /usr/bin/rsync
Here is how you would execute rsync:
The key thing for the remote host is to........
The solution is simple but strange, if you copy your /var/lib/mysql directory to another server and think it will work, be sure to check if you have /var/log/mysql and binary log files. If you do, the server will not work and will give you errors like below and crash without the proper log files.
UPDATE user SET password=password("newpass") WHERE user='root';
ERROR 2013 (HY000): Lost connection to MySQL server durin........
mysqldump: Couldn't execute 'show create table `general_log`': SHOW command denied to user 'user'@'localhost' for table 'general_log' (1142)
One of my clients almost found out the hard way, here is an unlikely situation that happened.
1.) Years ago the client had another VPS to which they backed up a BLOG nightly to an .sql file, what they forgot is that the file also contained all databases (they used the --all-databases option but forgot). So imagi........
ERROR 1146 (42S02): Table ‘mysql.servers’ doesn’t exist
This happened to me on a system running Centos with the REMI repo and a new version of Mysql 5 (which the official Centos/RHEL does not support on version 5).
The solution is just to run this program "mysql_upgrade", after that flush privileges will work.
One note is to secure MySQL, I don't know for sure but I believe you could login to MySQL remotely with no password during this operation (I'm not sure, maybe it doesn't accept blank passwords but I firewall MySQL port anyway and recommend you do the same).
First edit /etc/my.cf
Under the [mysqld] field add the following line somewhere:
Now restart mysql: service mysql restart or on Debian sty........
CREATE Database/Granting PrivilegesCREATE your first database
[code:1:0668cd8e74]CREATE database myfirstdb;
CREATE a user for your first database
This one creates the user 'sqladmin' and gives them 'usage' on your new database 'myfirstdb'
[code:1:0668cd8e74]mysql> GRANT usage on myfirstdb.* to sqladmin@localhost;
Query OK, 0 rows affected (0.00 sec)[/code:1:0668........
I have played around with Pound a little bit. It is a reverse proxy and load balancer in one, and it can be used as only a reverse proxy if you like. It is very simple to configure as either, and Pound even senses if one of the systems is down and stops sending requests to the dead server.
It supports SSL (but passes the request to the destination server unencrypted) and even the Apache log format. Pound is very simple, fast a........
This happened with a select statement which writes the result to an external file. I didn't remember the privilege "FILE" must be granted on *.* for this to work successfully, it wasn't actually an authentication issue as it implies since I was already in the SQL prompt and doing the query.
INTO OUTFILE '/tmp/result.txt'........
There is a nice mode that Samba supports which you can add to smb.conf
This way users can create and write files/directories without deleting (except I believe dirs can be deleted but only if there are no files inside).
It's too bad that Linux does not have built-in "write"/"delete" privileges and is something that even Windows 98 can trump.........
Backing MySQL Databases
Backing Up/Dumping All Mysql Databases To A Single File
mysqldump --all-databases -u admin -p > allmysqldatabases.sql
The "-all-databases" clause is pretty obvious isn't it? It means that it will backup all databases.
The "-u admin" means login using the user "admin", if you h........