Mysql - Revision history

Bacchas at 14:24, 7 October 2025

2025-10-07T14:24:50Z

← Older revision		Revision as of 14:24, 7 October 2025
Line 2:		Line 2:
	=[[MYSQL COMMANDS]]=		=[[MYSQL COMMANDS]]=
	=[[MYSQL DATABASE RESTORE]]=		=[[MYSQL DATABASE RESTORE]]=
			=[[MYSQL DATABASE BACKUP]]=

Bacchas at 14:14, 7 October 2025

2025-10-07T14:14:56Z

← Older revision		Revision as of 14:14, 7 October 2025
Line 1:		Line 1:
	=[[DADABIK]]=		=[[DADABIK]]=
	=[[MYSQL COMMANDS]]=		=[[MYSQL COMMANDS]]=
			=[[MYSQL DATABASE RESTORE]]=

Bacchas: Replaced content with "=DADABIK= =MYSQL COMMANDS="

2021-11-05T19:49:16Z

Replaced content with "=DADABIK= =MYSQL COMMANDS="

Show changes

Bacchas at 02:43, 20 August 2016

2016-08-20T02:43:05Z

← Older revision		Revision as of 02:43, 20 August 2016
Line 1:		Line 1:
	==MySql Commands My Fav ==		==MySql Commands My Fav ==
	~~<pre>~~
	Find and Replace the word "GaGa, Ladyy" in Artists and replace with "Lady GaGa		Find and Replace the word "GaGa, Ladyy" in Artists and replace with "Lady GaGa

	UPDATE Karaoke set Artists = replace(Artists, "GaGa, Lady", "Lady GaGa");		UPDATE Karaoke set Artists = replace(Artists, "GaGa, Lady", "Lady GaGa");

	UPDATE wp2_posts SET `post_content`= replace(`post_content`, "' rel='sexylightbox[]", "")		UPDATE wp2_posts
			SET `post_content`= replace(`post_content`, "' rel='sexylightbox[]", "")

	Delete		Delete

	Delete FROM `master2011` WHERE id < 1688		Delete FROM `master2011` WHERE id < 1688



	Update single record		Update single record

			UPDATE Karaoke
			SET Title = 'Size 10 Red Shoe',
	UPDATE Karaoke		Artists = 'Big Red Shoes
			Where DiskId=1234
	SET Title = 'Size 10 Red Shoe',

	Artists = 'Big Red Shoes

	Where DiskId=1234





	This will grant all priviliges to the user admin. will only work on local host not remote		This will grant all priviliges to the user admin. will only work on local host not remote

	GRANT ALL ON . TO admin@localhost WITH GRANT OPTION;		GRANT ALL ON . TO admin@localhost WITH GRANT OPTION;

	FLUSH PRIVILEGES; // this relloads the priviliges		FLUSH PRIVILEGES; // this relloads the priviliges

	use databasename // this loads the database		use databasename // this loads the database

	Show tables //show tables		Show tables //show tables

	Describe tablename//show fields		Describe tablename//show fields

	select * from tablename where Title Like "%fire%" //returns all instance of fire. you can also		select * from tablename where Title Like "%fire%" //returns all instance of fire. you can also

	select count (*) from tablename// returns number of records		select count (*) from tablename// returns number of records

	select * from tablename where Diskid="200"; // shows all records with disk id of 200		select * from tablename where Diskid="200"; // shows all records with disk id of 200

	select diskid, track, title, artists from tablename order by title // display defined columns. sort by title		select diskid, track, title, artists from tablename order by title // display defined columns. sort by title

	select artists, count(artists) from Karaoke Group by artists // counts the number of songs by that artists		select artists, count(artists) from Karaoke Group by artists // counts the number of songs by that artists

	select artists, group_concat(title order by title separator ' \| ' ) from tablename Group by artists // list them songs by artists		select artists, group_concat(title order by title separator ' \| ' ) from tablename Group by artists // list them songs by artists

	~~select * from passrequest where match (companyname, state) against ("texas") //column has to be fulltext search- fulltext search is when you setup the field proberties for the structure.~~

			select * from passrequest where match (companyname, state) against ("texas") //column has to be fulltext search- fulltext search is when you setup the field proberties for the structure.
			<pre>
	ALTER TABLE Karaoke AUTO_INCREMENT=15786		ALTER TABLE Karaoke AUTO_INCREMENT=15786
	Resets the auto_increment, in a situation where some of the most recently added rows were deleted. future insertions will be numbered from 15786 again (unless you still had rows numbered greater than 1234, and then the future insertions will start from the greatest number + 1 ).		Resets the auto_increment, in a situation where some of the most recently added rows were deleted. future insertions will be numbered from 15786 again (unless you still had rows numbered greater than 1234, and then the future insertions will start from the greatest number + 1 ).

Bacchas at 13:14, 9 August 2016

2016-08-09T13:14:20Z

← Older revision		Revision as of 13:14, 9 August 2016
Line 206:		Line 206:

	----		----
	[[#MySql Commands My Fav\|Back To Top]]-[[Main_Page\| Home~~]] - [[PHP\|Category~~]]		[[#MySql Commands My Fav\|Back To Top]]-[[Main_Page\| Home]]

Bacchas at 13:13, 9 August 2016

2016-08-09T13:13:38Z

@@ Line 1: / Line 1: @@
 ==Create mysql database in ubuntu shell==
 <pre>
@@ Line 143: / Line 205: @@
 </pre>
-==  ==
+----
-<pre>
+[[#MySql Commands My Fav|Back To Top]]-[[Main_Page| Home]] - [[PHP|Category]]

Bacchas: /* Create mysql database in ubuntu shell */

2016-08-08T16:34:54Z

Create mysql database in ubuntu shell

← Older revision		Revision as of 16:34, 8 August 2016
Line 29:		Line 29:

	mysql>		mysql>
			</pre>
			== securing MYSQL and setting a Password ==
			<pre>
			/usr/bin/mysql_secure_installation
			</pre>

			==Mysql Backup and Restore ==

			===Bakcup a single database===

			<pre>
			done from bash - no need to log in to mysql
			mysqldump -u root -ppassword sugarcrm > sugarcrm.sq

			syntax

			mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
			</pre>
			===Backup a single table===
			<pre>
			mysqldump -u root -ppassword databasename table > sugarcrm.sq
			</pre>


			===Backupup Multiple Database===
			<pre>
			log into mysql

			mysql -u root -pyourpassword

			mysql> show databases;

			this will give you a list of databases

			For example, if you want to take backup of both sugarcrm and bugs database, execute the mysqldump as shown below:

			mysqldump -u root -ptmppassword --databases bugs sugarcrm > bugs_sugarcrm.sql

			Verify the bugs_sugarcrm.sql dumpfile contains both the database backup.

			# grep -i "Current database:" /tmp/bugs_sugarcrm.sql

			-- Current Database: `mysql`

			-- Current Database: `sugarcrm`
			</pre>


			=== Backup all the databases:===
			<pre>
			The following example takes a backup of all the database of the MySQL instance.

			# mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql

			</pre>
			=== Backup a specific table:===
			<pre>
			In this example, we backup only the accounts_contacts table from sugarcrm database.

			mysqldump -u root -ptmppassword sugarcrm accounts_contacts > /tmp/sugarcrm_accounts_contacts.sql
			</pre>

			===Restore Database===
			<pre>
			mysql -u username -p database < dump_file.sql
			</pre>

			== Set / Change Mysql Password ==
			<pre>
			Set / change / reset the MySQL root password on Ubuntu Linux. Enter the following lines in your terminal.

			Stop the MySQL Server.
			sudo /etc/init.d/mysql stop


			Start the mysqld configuration.
			sudo mysqld --skip-grant-tables &


			Login to MySQL as root.
			mysql -u root mysql


			Replace YOURNEWPASSWORD with your new password!
			UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
			</pre>

			== Reset mysql password ==
			<pre>
			dpkg-reconfigure mysql-server-5.0

			or

			dpkg-reconfigure mysql-server-5.1

			Depending on which package you have installed.

			the will bring up the enter password screen for mysql
			</pre>

			== ==
			<pre>

			</pre>

			== Import csv file into Php Myadmin Database ==
			<pre>
			Structure has to match exactly or you will get an import error.

			"Invalid field count in csv input line 1"

			Make sure you count the fields. if there is 6 fileds (including the key) you must have 6 fields to import
			</pre>

			== ==
			<pre>

			</pre>

			== ==
			<pre>

	</pre>		</pre>

Bacchas: Created page with "==Create mysql database in ubuntu shell==

 On a default settings, mysql root user do not need a password to authenticate from localhost. In this case, ou can login as roo..."

2016-08-08T16:13:37Z

Created page with "==Create mysql database in ubuntu shell== <pre> On a default settings, mysql root user do not need a password to authenticate from localhost. In this case, ou can login as roo..."

New page

==Create mysql database in ubuntu shell==
<pre>
On a default settings, mysql root user do not need a password to authenticate from localhost. In this case, ou can login as root on your mysql server using:

$ mysql -u root

If a password is required, use the extra switch -p:
$ mysql -u root -p
Enter password:

Now that you are logged in, we create a database:
mysql> create database amarokdb;
Query OK, 1 row affected (0.00 sec)

We allow user amarokuser to connect to the server from localhost using the password amarokpasswd:
mysql> grant usage on *.* to amarokuser@localhost identified by 'amarokpasswd';
Query OK, 0 rows affected (0.00 sec)

And finally we grant all privileges on the amarok database to this user:
mysql> grant all privileges on amarokdb.* to amarokuser@localhost ;
Query OK, 0 rows affected (0.00 sec)

And that's it. You can now check that you can connect to the MySQL server using this command:
$ mysql -u amarokuser -p'amarokpasswd' amarokdb
Your MySQL connection id is 12
Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
</pre>