How to export a MySQL database to JSON?

If you have Ruby, you can install the mysql2xxxx gem (not the mysql2json gem, which is a different gem):

$ gem install mysql2xxxx

and then run the command

$ mysql2json --user=root --password=password --database=database_name --execute "select * from mytable" >mytable.json

The gem also provides mysql2csv and mysql2xml. It's not as fast as mysqldump, but also doesn't suffer from some of mysqldump's weirdnesses (like only being able to dump CSV from the same computer as the MySQL server itself)

It may be asking too much of MySQL to expect it to produce well formed json directly from a query. Instead, consider producing something more convenient, like CSV (using the INTO OUTFILE '/path/to/output.csv' FIELDS TERMINATED BY ',' snippet you already know) and then transforming the results into json in a language with built in support for it, like python or php.

Edit python example, using the fine SQLAlchemy:

class Student(object):    '''The model, a plain, ol python class'''    def __init__(self, name, email, enrolled):        self.name = name        self.email = email        self.enrolled = enrolled    def __repr__(self):        return "<Student(%r, %r)>" % (self.name, self.email)    def make_dict(self):        return {'name': self.name, 'email': self.email}import sqlalchemymetadata = sqlalchemy.MetaData()students_table = sqlalchemy.Table('students', metadata,        sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),        sqlalchemy.Column('name', sqlalchemy.String(100)),        sqlalchemy.Column('email', sqlalchemy.String(100)),        sqlalchemy.Column('enrolled', sqlalchemy.Date)    )# connect the database.  substitute the needed values.engine = sqlalchemy.create_engine('mysql://user:pass@host/database')# if needed, create the table:metadata.create_all(engine)# map the model to the tableimport sqlalchemy.ormsqlalchemy.orm.mapper(Student, students_table)# now you can issue queries against the database using the mapping:non_students = engine.query(Student).filter_by(enrolled=None)# and lets make some json out of it:import jsonnon_students_dicts = ( student.make_dict() for student in non_students)students_json = json.dumps(non_students_dicts)

Another possibility is using the MySQL Workbench.

There is a JSON export option at the object browser context menu and at the result grid menu.

More information on MySQL documentation: Data export and import.